How do I use select with date condition?

Question

Hello,

In sqlserver, how do I compare dates? For example:

Select * from Users where RegistrationDate >= '1/20/2009'

(RegistrationDate is datetime type)

Thanks

Answer 1

If you put in Select * From Users Where RegistrationDate >= '1/20/2009' it will automatically convert the string '1/20/2009' into the DateTime format for a date of 1/20/2009 00:00:00. So by using >= you should get every user whose registration date is 1/20/2009 or more recent.

Edit: I put this in the comment section but I should probably link it here as well. This is an article detailing some more in depth ways of working with DateTime's in you queries: http://www.databasejournal.com/features/mssql/article.php/2209321/Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges.htm

Answer 2

Select * from Users where RegistrationDate >= CONVERT(datetime, '01/20/2009', 103)

is safe to use, independent of the date settings on the server.

The full list of styles can be found here.

Answer 3

I always get the filter date into a datetime, with no time (time= 00:00:00.000)

DECLARE @FilterDate  datetime --final destination, will not have any time on it
DECLARE @GivenDateD  datetime --if you're given a datetime
DECLARE @GivenDateS  char(23) --if you're given a string, it can be any valid date format, not just the yyyy/mm/dd hh:mm:ss.mmm that I'm using

SET @GivenDateD='2009/03/30 13:42:50.123'
SET @GivenDateS='2009/03/30 13:42:50.123'

--remove the time and assign it to the datetime
@FilterDate=dateadd(dd, datediff(dd, 0, @FilterDateD), 0)
--OR
@FilterDate=dateadd(dd, datediff(dd, 0, @FilterDateS), 0)

You can use this WHERE clause to then filter:

WHERE ColumnDateTime>=@FilterDate AND ColumnDateTime<@FilterDate+1

this will give all matches that are on or after the beginning of the day on 2009/03/30 up to and including the complete day on 2009/03/30

you can do the same for START and END filter parameters as well. Always make the start date a datetime and use zero time on the day you want, and make the condition ">=". Always make the end date the zero time on the day after you want and use "<". Doing that, you will always include any dates properly, regardless of the time portion of the date.

Answer 4

Another feature is between:

Select * from table where date between '2009/01/30' and '2009/03/30'