How to compare date and time field in sql query?

[tasawer]

Hi,
I am practising SQL Serverso my question is from the basics. I could not get the right answer from other search engines but i am sure i will be rightly guided here.

date and time are stored in a single field 'gps_date' and each field shows details like this: 2011-02-05 10:21:47.000
I need to filter the date and time field to list:

• Todays Data

• Data Between Two Dates

my very basic sql statement is show below. How can I add the above features to it.

SELECT * FROM gps_data where gps_device_id = '0123456'
 

[gpl]

I frequently use this to strip off the time part (it gets converted to midnight)

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

so your query would be

SELECT * FROM gps_data where gps_device_id = '0123456'
and
DATEADD(D, 0, DATEDIFF(D, 0, gps_date))=DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
 

likewise for a daterange,

SELECT * FROM gps_data where gps_device_id = '0123456'
and
DATEADD(D, 0, DATEDIFF(D, 0, gps_date)) between convert(datetime,'2011-01-21') 
and convert(datetime, '2011-02-22')
 

Im pretty sure that putting the date into military format you dont need to specify a format code.
The above code pulls out all data between 2011-01-21 and 2011-02-21 (2011-02-22 is the same as the last moment of the 21st Feb)

[ck9663]

Here, read this for additional info...

Good Luck!!!

~~ CK

[tasawer]

Thanks for both replies..


great help