CODEWITHSUNDEEP
sqlsql-server
Cody J. Mathis
Cody J. Mathis

Reputation: 652

Using Cast As Date vs DateTime for a Between

Is this:

between cast(date as date) and cast(date1 as date)

The same as:

between '01/01/01 00:00:00' and '01/01/01 11:59:59'

I've been told that when comparing date time values in SQL Server that if I wanted to return the PROPER results I should ALWAYS include a time component with my date.

Upvotes: 1

Views: 5105

Answers (3)

MJH
MJH

Reputation: 1750

No, 

between cast(date as date) and cast(date1 as date)

is the same as:

between '20180310 00:00:00' and '20180315 00:00:00'

Between is inclusive of the values, it's the equivalent of:

@Dt >= '20180310 00:00:00' 
and @Dt <= '20180315 00:00:00'

Upvotes: 1

SMor
SMor

Reputation: 2862

Gordon is spot on. And I can demonstrate why with your own words. You wrote: 

between '01/01/01 00:00:00' and '01/01/01 11:59:59'

To people who do not use 24 hour time regularly, this can easily appear to be correct logic. Unfortunately it has 2 problems. First and foremost, without an "AM/PM" designation, the default interpretation is noon. This is an easy mistake to make and one that might not be found during testing (developers are lazy - trust that).

Secondly, you specifically state datetime. What is the maximum possible time component of that datatype? Is it not what you have provided; rather it is 23:59:59.997. And what happens if someone changes the datatype to a more precise datetime2?

You might easily discard this concern as unlikely - and it is. But such edge cases have a habit of showing up eventually - and usually in situations where something has gone wrong. It is better to write code that is correct for the datatypes in use and not dependent on assumptions and luck.

I'll give a link to Tibor's datetime guide - whose site also contains lots of other excellent content worth reading when possible.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Don't use between with dates. It is just risky. In general, the right syntax to use is:

where dtcol >= @date1 and
      dtcol < dateadd(day, 1, @date2)

Note the direction of the inequalities. This works for columns that are both dates and date/times. So, it just works.

Aaron Bertrand has an excellent blog post on this, What do BETWEEN and the devil have in common?.

Upvotes: 5

Related Questions