Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sunil Chandurkar » DATETIME Puzzle
Add to briefcase
33 posts, Page 3 of 4
«««1234»»

DATETIME Puzzle Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Hugo Kornelis
Posted Thursday, November 17, 2011 1:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062
Ola L Martins-329921 (11/17/2011)
GPO (11/17/2011)
There are good reasons to avoid using BETWEEN when comparing two datetimes. Particularly in the example the answer uses. You should be using the various symbols for GREATER THAN and LESS THAN.

No, I don't think you can (if not reverting to subqueries...)

The "AND" operator will check against the date-columns seperately - not simultaneously which in the case with "BETWEEN".
Using GREATER THAN and LESS THAN in the above question would actually return all the records.

(Sorry, being a bit rusty I'm not entirely sure this is correct, but I've been there, done that and bought the friggin' t-shirt...)

I'm afraid I don't understand you.
WHERE MyDate BETWEEN '20111101' AND '20111130' will not return everything for november - it will return everything for november 1 through 29, plus everything that happened at november 30, exactly midnight.
WHERE MyDate BETWEEN '20111101' AND '20111201' will also not return everything for november - it will return everything for november, plus everything that happened at december 1, exactly midnight.
The only correct code to return everything for november, nothing less, nothing more, is to use seperate >= and < tests:
WHERE MyDate >= '20111101' AND MyDate < '20111201'

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1207861
GilaMonster
Posted Thursday, November 17, 2011 2:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021
Ola L Martins-329921 (11/17/2011)
The "AND" operator will check against the date-columns seperately - not simultaneously which in the case with "BETWEEN".
Using GREATER THAN and LESS THAN in the above question would actually return all the records.


BETWEEN is completely equivalent to a combination of <= and >=

SomeColumn BETWEEN @Var1 and @Var2 is exactly the same as SomeColumn >= @Var1 and SomeColumn <= @Var2. In fact, the SQL parser converts the BETWEEN into the <= and >= form during parsing (pre-execution)

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1207962
SanDroid
Posted Thursday, November 17, 2011 3:02 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
GPO (11/17/2011)
There are good reasons to avoid using BETWEEN when comparing two datetimes. Particularly in the example the answer uses. You should be using the various symbols for GREATER THAN and LESS THAN.


I wonder what your opinion is on INTERSECT, EXCEPT, or Common Table Expressions?

One BETWEEN statement easier to read than two or more GREATER THAN and LESS THAN statements.
Post #1207966
SQLRNNR
Posted Thursday, November 17, 2011 3:40 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337
GilaMonster (11/17/2011)
Ola L Martins-329921 (11/17/2011)
The "AND" operator will check against the date-columns seperately - not simultaneously which in the case with "BETWEEN".
Using GREATER THAN and LESS THAN in the above question would actually return all the records.


BETWEEN is completely equivalent to a combination of <= and >=

SomeColumn BETWEEN @Var1 and @Var2 is exactly the same as SomeColumn >= @Var1 and SomeColumn <= @Var2. In fact, the SQL parser converts the BETWEEN into the <= and >= form during parsing (pre-execution)


I have seen this myth propagated from time to time (use lt and gt in lieu of between).


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1207988
GPO
Posted Thursday, November 17, 2011 6:21 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 657, Visits: 1,309
If I were to write the query (which was )

SELECT *
FROM #DateTest
WHERE SampleDate BETWEEN  @DATE1 AND @DATE2

I would have written it

SELECT *
FROM #DateTest
WHERE SampleDate >=  @DATE1 
      AND SampleDate < (@DATE2 + 1)

That way I don't get into strife when my data contains a time that is greater than 23:59 and less than the start of the next day. Obviously I would have to remember to sanitise my inputs to ensure that the @date2 value coming in was a whole date (had no time fraction). Sorry I wasn't clearer earlier.

EDIT! EDIT! EDIT! I would have written it this way assuming of course that I wanted to return all the activity on @DATE2.



One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1208045
GPO
Posted Thursday, November 17, 2011 6:43 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 657, Visits: 1,309
The only correct code to return everything for november, nothing less, nothing more, is to use seperate >= and < tests:
WHERE MyDate >= '20111101' AND MyDate < '20111201'


Massive thanks Hugo for putting this much more elegantly than I. Sorry to everyone else



One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1208050
Ola L Martins-329921
Posted Friday, November 18, 2011 2:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 18, 2012 3:16 AM
Points: 264, Visits: 178
Hugo Kornelis (11/17/2011)
Ola L Martins-329921 (11/17/2011)
GPO (11/17/2011)
There are good reasons to avoid using BETWEEN when comparing two datetimes. Particularly in the example the answer uses. You should be using the various symbols for GREATER THAN and LESS THAN.

No, I don't think you can (if not reverting to subqueries...)

The "AND" operator will check against the date-columns seperately - not simultaneously which in the case with "BETWEEN".
Using GREATER THAN and LESS THAN in the above question would actually return all the records.

(Sorry, being a bit rusty I'm not entirely sure this is correct, but I've been there, done that and bought the friggin' t-shirt...)

I'm afraid I don't understand you.
WHERE MyDate BETWEEN '20111101' AND '20111130' will not return everything for november - it will return everything for november 1 through 29, plus everything that happened at november 30, exactly midnight.
WHERE MyDate BETWEEN '20111101' AND '20111201' will also not return everything for november - it will return everything for november, plus everything that happened at december 1, exactly midnight.
The only correct code to return everything for november, nothing less, nothing more, is to use seperate >= and < tests:
WHERE MyDate >= '20111101' AND MyDate < '20111201'


2 ×
I confused this with another project I was working on a loooong time ago. No wonders I got it mixed up.
Thx Hugo for the explanation. And thx GilaMonster for explaining the parsing-part.
I will now crawl back under the stone where I came from... still blushing...
Post #1208158
Hugo Kornelis
Posted Friday, November 18, 2011 2:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062
GPO (11/17/2011)
I would have written it

SELECT *
FROM #DateTest
WHERE SampleDate >=  @DATE1 
      AND SampleDate < (@DATE2 + 1)


That would have worked for the "old" date/time related data types (datetime and smalldatetime). But for the new date/time datatypes (datetime2, date, datetimeoffset), adding a day by using +1 is not supported. I suggest you switch to using DATEADD(day, 1, @DATE2) instead - that works for all date/tme datatypes, so your code won't break if you one day change to another data type.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1208161
L' Eomot Inversé
Posted Monday, November 21, 2011 4:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 7,112, Visits: 7,185
Good question, which illustrates a point that has been known to trap inexperienced people and may help some of them for falling into the trap of thinking that when comparing a date and a datetime the date is implicitly converted to a date rather than the date being implicitly converted to a datetime.

But the description
I am extending Dwayne Dibley's question to show how 'Time' part in date is considered by SQL server

at the front of the question is a little inaccurate, albeit rather amusing. There is no 'Time' part in date, the issue being illustrated is actually what time part will be used in the result of an implicit conversion of a date (which has no time part) to datetime (which has to have one) [or for that matter to datetime2, or smalldatetime - all three datetime types have higher type precedence that date].

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.
Post #1209151
L' Eomot Inversé
Posted Monday, November 21, 2011 4:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 7,112, Visits: 7,185
Thomas Abraham (11/17/2011)
After Che Guevara's question from yesterday, I'll take the easy two points.

Drat! I'll have to change my avatar to something less recognisable! Either that or change my nickname to "El comandante" so that it matches the avatar.

Edit: I changed it. Still unmatched, though.

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.
Post #1209153
« Prev Topic | Next Topic »

Add to briefcase
33 posts, Page 3 of 4
«««1234»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.