Date Comparison in TSQL

Question

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.

So the answer is simple, you compare with `2015-12-14T00:00:00` and don't have exact match — Lukasz Szozda, Dec 14 '15 at 17:46

peter.petrov · Accepted Answer · 2015-12-14T17:44:28.127

7

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.

edited Dec 14 '15 at 17:44

answered Dec 14 '15 at 17:43

peter.petrov

38,363
16
94
159

it does actually contain a time part (it is a datetime type) , but it works well with < and > but not with = – t_plusplus Dec 14 '15 at 17:44
8

Well, the time part doesn't mess up the `<` or `>` comparisons. Think about it, it's normal. For example '20140101 3 PM' is still bigger than '20140101' but it's not equal to it. – peter.petrov Dec 14 '15 at 17:45
If you have an index on `CreatedDate` the best way to execute the query is: `Select Name, CreatedDate from EmployeeTable where CreatedDate >= '20151214' and CreatedDate < '20151215'`, otherwise MSSQL will probably run a full table scan. – Wagner DosAnjos Dec 14 '15 at 17:50

score 1 · Answer 2 · answered Dec 14 '15 at 17:45

1

DATEDIFF is the preferred way of checking equality of dates in SQL, unless you use the newer DATE type (instead of DATETIME).

Casting to DATE removes any time portion.

DATEDIFF is probably also more robust (than <>=)

answered Dec 14 '15 at 17:45

Grantly

2,546
2
21
31

1

AFAIK, DATEDIFF is slower, if we can use < or >, we'd better use them. – peter.petrov Dec 14 '15 at 17:50
Actually I think its not slower, especially for equality testing. DATEDIFF is optimized very well. For <> you may be correct. Unless you have some performance stats :) – Grantly Dec 14 '15 at 17:51
I don't know. But I've read this in a good book. – peter.petrov Dec 14 '15 at 17:53
@Grantly: do you have any references / benchmarks on `DATEDIFF` performance compared to other methods? – Wagner DosAnjos Dec 14 '15 at 17:54
Relevant: http://stackoverflow.com/questions/20476637/which-is-better-to-compare-dates-or-datediff – AHiggins Dec 14 '15 at 17:56
This is one off the cuff, not very scientific nor conclusive. http://www.ideaexcursion.com/2009/02/17/efficiently-query-the-date-in-datetime/ ... Not that the OP question is about speed, but having to CAST a string to a DATE or DATETIME inherently is adding more complexity. DATEDIFF does this faster as you do not need the CAST – Grantly Dec 14 '15 at 17:59
(Although I do tend to agree, for simplicity <> are better. That DATEDIFF ignores indexes is surprising but possible. For equality testing - to eliminate using a CAST - I think DATEDIFF would compare favourably) – Grantly Dec 14 '15 at 18:02
Performance is not that good, see http://sqltouch.blogspot.se/2013/11/datediff-function-common-performance.html. DATEADD might be a better option when used on the RHS. (Better than using a CAST anyway) – Grantly Dec 14 '15 at 18:19

Date Comparison in TSQL

2 Answers2