Tuesday, July 29, 2003

SQL: Date Searching

Just some useful tips for searching on date fields (Microsoft SQL Server 2000+):

You can use the LIKE operator to do a search:

SELECT * FROM [tablename] WHERE [datecolumn] LIKE '%Aug%2003%'

This would match everything that had August 2003 in datecolumn e.g. August 12 2003, August 18 2003

The other way is to use DATEPART as follows:

SELECT * FROM [tablename] WHERE DATEPART(month,[datecolumn]) = 8 AND DATEPART(year,[datecolumn]) = 2003

The final way is to use MONTH and YEAR:

SELECT * FROM [tablename] WHERE MONTH([datecolumn]) = 8 AND YEAR([datecolumn]) = 2003

N.B. If you use DATEPART, MONTH, or YEAR, the supplied value has to be numeric (8 instead of August)

No comments: