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

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 2

The LIKE operator does not work with date parts like month or date but the DATEPART operator does.

Command to find out all accounts whose Open Date was on the 1st:

SELECT * 
  FROM Account 
 WHERE DATEPART(DAY, CAST(OpenDt AS DATE)) = 1`

*CASTING OpenDt because it's value is in DATETIME and not just DATE.

Answer 3

There is a very flaky coverage of the LIKE operator for dates in SQL Server. It only works using American date format. As an example you could try:

... WHERE register_date LIKE 'oct 10 2009%'

I've tested this in SQL Server 2005 and it works, but you'll really need to try different combinations. Odd things I have noticed are:

• You only seem to get all or nothing for different sub fields within the date, for instance, if you search for 'apr 2%' you only get anything in the 20th's - it omits 2nd's.

• Using a single underscore '_' to represent a single (wildcard) character does not wholly work, for instance, WHERE mydate LIKE 'oct _ 2010%' will not return all dates before the 10th - it returns nothing at all, in fact!

• The format is rigid American: 'mmm dd yyyy hh:mm'

I have found it difficult to nail down a process for LIKEing seconds, so if anyone wants to take this a bit further, be my guest!

Hope this helps.

Answer 4

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