SQL Server datetime LIKE select?

sql-server sql-like
Paisal picture Paisal · Oct 27, 2009 · Viewed 279.9k times · Source

in MySQL 

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

What is the syntax in SQL Server?

Answer

Ralph Lavelle picture Ralph Lavelle · Oct 27, 2009

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.

Related questions

LIKE vs CONTAINS on SQL Server

Which one of the following queries is faster (LIKE vs CONTAINS)? SELECT * FROM table WHERE Column LIKE '%test%'; or SELECT * FROM table WHERE Contains(Column, "test");

Read More
SQL 'LIKE' query using '%' where the search criteria contains '%'

I have an SQL query as below. Select * from table where name like '%' + search_criteria + '%' If search_criteria = 'abc', it will return data containing xxxabcxxxx which is fine. But if my search_criteria = 'abc%', it …

Read More
SQL- Ignore case while searching for a string

I have the following data in a Table PriceOrderShipped PriceOrderShippedInbound PriceOrderShippedOutbound In SQL I need to write a query which searches for a string in a table. While searching for a string it should ignore case. For the below mentioned …

Read More