Comparing Dates in SQL
Published
15 Apr 2008
15 Apr 2008
Updated
19 Jul 2009
19 Jul 2009
Some useful sites:
Get the start date by:
Replace [datetime] with the date (eg.
This casts the datetime as a float, gets the floor (chops off the decimal) and converts back to a date. This will always give you midnight for the day. Use a greater than or equal to comparison, as it needs to include midnight.
To get the end date:
Again, replace [datetime] with the date. This will return midnight on the following day, so use a less than comparison. This method seems to be slightly better than converting to a varchar, as it will not accidentally compare dates lexigraphically (comparing each digit separately).
blog comments powered by Disqus
- date and time functions using datediff and datepart
- getting the date from a datetime
Get the start date by:
- SELECT CAST(FLOOR( CAST( [DATETIME] AS FLOAT ) )AS DATETIME)
Replace [datetime] with the date (eg.
GETDATE() for the current date).This casts the datetime as a float, gets the floor (chops off the decimal) and converts back to a date. This will always give you midnight for the day. Use a greater than or equal to comparison, as it needs to include midnight.
To get the end date:
- SELECT CAST(CEILING( CAST( [DATETIME] AS FLOAT ) )AS DATETIME)
Again, replace [datetime] with the date. This will return midnight on the following day, so use a less than comparison. This method seems to be slightly better than converting to a varchar, as it will not accidentally compare dates lexigraphically (comparing each digit separately).
