Starts from version 2008 there are special types to store only date or time. These are Date and Time accordingly. But if a program should support old versions of SQL server then both date and time are stored together in DateTime field. It looks like for example ‘2012-09-02 02:07:38.430’.
Say we need to find all records for one day 2012-09-02.
The simple string comparison works in 2000, 2005 and 2008 versions if date format is correct.
SELECT * FROM SpecialDay WHERE [Date]='2012-09-02'
But this is not safe. And starts from SQL server 2012 this query will not return anything even with the correct same time as in database records.
SELECT * FROM SpecialDay WHERE [Date]='2012-09-02 00:00:00.000'
Then I learned to do it better way:
DECLARE @date DATETIME; SET @date = CONVERT(DATETIME, '2012-09-02', 120); SELECT * FROM SpecialDay WHERE [Date] >= @date AND [Date] < DATEADD(d, 1, @date)
I tried it in all versions of SQL server starts from 2000 and works everywhere.
Here the statement
CONVERT(DATETIME, '2012-09-02', 120) converts a string to a date format type 120. This is so caled ODBC-format ‘yyyy-mm-dd hh:mm:ss’ 24-hours. String can be converted to Date-type can be converted with any separator character: slash, point or whatever.
Here is the list of supported date and time formats for CONVERT statement.