MSSQL: WHERE LIKE equivalent for DateTime

I don’t often use SQL queries but sometimes I have to. And every time it’s the same – I use search engines to find documentation, especially about work with datetime fields. During these readings I found out at least 3 ways to select records with date I need.

The task is to get records about employees who were born in September 1990.

1. The first way is using BETWEEN:

select * from tbl_User WHERE Birthday between '1990-09-01' AND '1990-09-30'

1	select * from tbl_User WHERE Birthday between '1990-09-01' AND '1990-09-30'

2. Using function DATEPART – it has a lot of parameters, but the condition is some longer.

select * from tbl_User WHERE DATEPART(YEAR, Birthday) = 1990 AND DATEPART( MONTH, Birthday) = 9

1	select * from tbl_User WHERE DATEPART(YEAR, Birthday) = 1990 AND DATEPART( MONTH, Birthday) = 9

3. Using CONVERT function:

select * from tbl_User WHERE CONVERT(Varchar, Birthday, 20) LIKE '1990-09%'

1	select * from tbl_User WHERE CONVERT(Varchar, Birthday, 20) LIKE '1990-09%'

Which of them to choose - I advice you to see a plan of request and make a decision after.

23 June 2015, admin
SQL
Tags: SQL query, Transact-SQL
Comments: (0)

MSSQL: WHERE LIKE equivalent for DateTime

No Comments »

Leave a comment

New posts