CODEWITHSUNDEEP
sqlcastingsql-server-2014
Addison Swgr Lam
Addison Swgr Lam

Reputation: 1

SQL Server 2014 - Cast Date effecting results

What is the different between the two conditions below? DateRecordCreated is a DTM field.

  1. cast([DateRecordCreated] as date) >= '2014-07-04' and cast([DateRecordCreated] as date) <= '2014-07-10'

  2. [DateRecordCreated] >= '2014-07-04' and [DateRecordCreated] <= '2014-07-10'

They look like the same condition but give me different results. #2 gives me a lower number.

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

The second one has a time component. So, DateRecordCreated is being compared to the date 2014-07-10 without the time (in the second case). That means that any value not exactly at midnight is going to fail the test.

The first version chops off the time component, so it would include all times on that date.

As a general rule, I would write this logic as:

[DateRecordCreated] >= '2014-07-04' and [DateRecordCreated] < '2014-07-11'

Aaron Bertrand actually has a very useful blog on this topic. I would recommend that you read it.

Upvotes: 2

Related Questions