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.