CODEWITHSUNDEEP
sqlsql-servert-sql
sanket munj
sanket munj

Reputation: 29

how to use combine date part in search statement of sql server?

select * 
from employee
where DATEPART(MM ,empDOJ) + DATEPART(yy,empDOJ) < 2013 + 5

I want employee detail from table "employee" where date of join of employee is less than may 2013 but above query is not working properly and "empdoj" is date in sql

Upvotes: 0

Views: 197

Answers (2)

roman
roman

Reputation: 117485

correct way of doing this would be

select *
from employee
where empDOJ < convert(date, '20130501', 112)

But you can use string '20130501' or '2013-05-01' because when you compare it to date column, SQL Server will convert data types according to data type priorities. Since date type is higher priority than varchar type, SQL will convert varchar to date implicitly:

select *
from employee
where empDOJ < '20130501'

Note that using functions on your column prevent from using indexes on this column, so when you're writing where datepart(MM ,empDOJ) = 5 index (if you have index on empDOJ column) will not be used.

Upvotes: 1

Max
Max

Reputation: 7100

SELECT *
FROM EMPLOYEE
WHERE EMPDOJ < '2013-05-01'

Upvotes: 0

Related Questions