Wednesday, June 01, 2005

SQL : Comparing dates with SQL server

The date is actually stored as "date & time".

So a search:

SELECT * FROM Table
WHERE Table_Date > GETDATE()

will not find any articles with a Table_Date of today - because the GETDATE() time now is > the time the record was inserted into the table.

To do the compare properly, only the date portions must be compared i.e.

SELECT * FROM Table
WHERE (CONVERT(varchar(10), Table_Date, 112) >= CONVERT(varchar(10), GETDATE(), 112))

112 is the ISO standard style to ensure the correct compare results.

Enjoy!

No comments: