Search code examples
sqlsql-serversql-server-2012sqldatetime

Date Comparison in TSQL

Why is it that each of the following returns records:

SELECT Name, CreatedDate 
FROM EmployeeTable 
WHERE CreatedDate < '20151214'

SELECT Name, CreatedDate 
FROM EmployeeTable 
WHERE CreatedDate > '20151214'

While the following query doesn't?

SELECT Name, CreatedDate 
FROM EmployeeTable 
WHERE CreatedDate = '20151214'

I had to use a CAST with the equality operator to get the results I wanted: 

SELECT Name, CreatedDate 
FROM EmployeeTable 
WHERE CAST(CreatedDate as DATE) = '2015-12-14'

Is there any way to use the equality operator without having to use a cast?

CreatedDate is of type DATETIME, and includes times.

Solution

  • Probably CreatedDate contains a time part (e.g. is of type datetime). That's why the = doesn't work out of the box. When you cast it to date the time part is removed and then the = comparison works OK.