SQL Server datetime LIKE select ?

Question

in MySQL

select * from record where register_date like '2009-10-10%'

What is the syntax in SQL Server?

Thank you.

Answer 1

There's no direct support for LIKE operator against DATETIME variables, but you can always cast the DATETIME to a VARCHAR:

SELECT (list of fields) FROM YourTable
WHERE CONVERT(VARCHAR(25), register_date, 126) LIKE '2009-10-10%'

Check the MSDN docs for a complete list of available "styles" in the CONVERT function.

Marc

Answer 2

If you do that, you are forcing it to do a string conversion. It would be better to build a start/end date range, and use:

declare @start datetime, @end datetime
select @start = '2009-10-10', @end = '2009-11-10'
select * from record where register_date >= @start
           and register_date < @end

This will allow it to use the index (if there is one on register_date), rather than a table scan.

Answer 3

You can use CONVERT to get the date in text form. If you convert it to a varchar(10), you can use = instead of like:

select *
from record
where CONVERT(VARCHAR(10),register_date,120) = '2009-10-10'

Or you can use an upper and lower boundary date, with the added advantage that it could make use of an index:

select *
from record
where '2009-10-10' <= register_date
and register_date < '2009-10-11'

Answer 4

You could use the DATEPART() function

select * from record 
where (DATEPART(yy, register_date) = 2009
AND DATEPART(mm, register_date) = 10
AND DATEPART(dd, register_date) = 10)

I find this way easy to read, as it ignores the time component, and you don't have to use the next day's date to restrict your selection. You can go to greater or lesser granularity by adding extra clauses, using the appropriate DatePart code, e.g.

AND DATEPART(hh, register_date) = 12)

to get records made between 12 and 1.

Answer 5

For a healthy discussion on this (including why it's bad to treat DATETIME values like strings, and why it can be bad to use "BETWEEN" or ">= AND <="), see: http://is.gd/4EgDF