SQL Server datetime LIKE select?

in MySQL

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

What is the syntax in SQL Server?

Solution 1:

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.

Consult the MSDN DATEPART docs for the full list of valid arguments.

Solution 2:

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

Solution 3:

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.

Solution 4:

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'

Solution 5:

Unfortunately, It is not possible to compare datetime towards varchar using 'LIKE' But the desired output is possible in another way.

    select * from record where datediff(dd,[record].[register_date],'2009-10-10')=0