SQL for time -- sql server new users

Arnie Rowland
9/19/2006 1:40:40 PM

If it is truly a datetime datatype, and you are only putting in the time =
portion, then all dates will be '01/01/1900'

If you attempt to convert or cast the datetime field in the where =
clause, your query will not be able to use any indexes you may have on =
the column.=20

I suggest that you may wish to add cast the time criteria in the where =
clause as a datetime, thereby comparing it to the form of the data =
itself. The cast( X AS datetime ), when X is in the form of '8:00 PM' =
will have the effect of making it '1900/01/01 08:00:00.000 PM', and that =
should match the form of the stored time values.

For example:

SELECT=20
Column1
, Column2
, Column3
, etc
FROM MyTable
WHERE TimeColumn BETWEEN cast( X AS datetime ) AND cast( Y AS datetime =
)

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

[quoted text, click to view]

Alejandro
9/19/2006 3:52:40 PM

Hi,

I am using ASP/VB and MS SQL 2000 DB.

I have a date field that just holds a 'time', say 8:00 am
Then I have an ASP page where the user enters the time to compare, they will
search for results that have 'time' field between say 7 am 10, in this case
the record with 8:00 am should be displayed.
How can I query that field and 'extract' only the 'time' ?

Select * from table where timefield between X and Y ?

But only the time part of it so that it wont compare the date.

Thanks for the help.

Aleks

Alejandro
9/19/2006 4:46:16 PM

Thanks guys, Ill test tonight.

A
[quoted text, click to view]
If it is truly a datetime datatype, and you are only putting in the =
time portion, then all dates will be '01/01/1900'

If you attempt to convert or cast the datetime field in the where =
clause, your query will not be able to use any indexes you may have on =
the column.=20

I suggest that you may wish to add cast the time criteria in the where =
clause as a datetime, thereby comparing it to the form of the data =
itself. The cast( X AS datetime ), when X is in the form of '8:00 PM' =
will have the effect of making it '1900/01/01 08:00:00.000 PM', and that =
should match the form of the stored time values.

For example:

SELECT=20
Column1
, Column2
, Column3
, etc
FROM MyTable
WHERE TimeColumn BETWEEN cast( X AS datetime ) AND cast( Y AS =
datetime )

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

[quoted text, click to view]

Arnie Rowland
9/19/2006 6:08:34 PM

It would help us better assist you if you could include table DDL, query =
strategy used so far, sample data in the form of INSERT statements, and =
an illustration of the desired results. (For help with that refer to: =
http://www.aspfaq.com/5006 )=20

=20

The less 'set up' work we have to do, the more likely you are going to =
have folks tackle your problem and help you. Without this effort from =
you, we are just playing guessing games.=20

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

[quoted text, click to view]
I just realized what I need is to compare getdate() to be between =
start time and end time ... how can I do this ? I tried the following =
but didnt work, I get no results.

SELECT *
FROM Userlogin
WHERE getdate() BETWEEN cast( loginstartime AS datetime ) AND cast( =
loginendtime AS datetime )

Anything wrong ?

A

[quoted text, click to view]
If it is truly a datetime datatype, and you are only putting in the =
time portion, then all dates will be '01/01/1900'

If you attempt to convert or cast the datetime field in the where =
clause, your query will not be able to use any indexes you may have on =
the column.=20

I suggest that you may wish to add cast the time criteria in the =
where clause as a datetime, thereby comparing it to the form of the data =
itself. The cast( X AS datetime ), when X is in the form of '8:00 PM' =
will have the effect of making it '1900/01/01 08:00:00.000 PM', and that =
should match the form of the stored time values.

For example:

SELECT=20
Column1
, Column2
, Column3
, etc
FROM MyTable
WHERE TimeColumn BETWEEN cast( X AS datetime ) AND cast( Y AS =
datetime )

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

[quoted text, click to view]

Alejandro
9/19/2006 8:47:12 PM

I just realized what I need is to compare getdate() to be between start =
time and end time ... how can I do this ? I tried the following but =
didnt work, I get no results.

SELECT *
FROM Userlogin
WHERE getdate() BETWEEN cast( loginstartime AS datetime ) AND cast( =
loginendtime AS datetime )

Anything wrong ?

A

[quoted text, click to view]
If it is truly a datetime datatype, and you are only putting in the =
time portion, then all dates will be '01/01/1900'

If you attempt to convert or cast the datetime field in the where =
clause, your query will not be able to use any indexes you may have on =
the column.=20

I suggest that you may wish to add cast the time criteria in the where =
clause as a datetime, thereby comparing it to the form of the data =
itself. The cast( X AS datetime ), when X is in the form of '8:00 PM' =
will have the effect of making it '1900/01/01 08:00:00.000 PM', and that =
should match the form of the stored time values.

For example:

SELECT=20
Column1
, Column2
, Column3
, etc
FROM MyTable
WHERE TimeColumn BETWEEN cast( X AS datetime ) AND cast( Y AS =
datetime )

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

sql server new users : SQL for time