SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sunil Chandurkar / DATETIME Puzzle / Latest Posts

RE: DATETIME Puzzle

Danny Ocean — Mon, 19 Dec 2011 05:22:36 GMT

good question with good expatiation

RE: DATETIME Puzzle

Sunil Chandurkar — Mon, 28 Nov 2011 02:17:10 GMT

Thanks to all who posted comments. I myself got to learn more by reading all the comments.

RE: DATETIME Puzzle

Paul White — Tue, 22 Nov 2011 17:49:50 GMT

[quote][b]GilaMonster (11/17/2011)[/b][hr][quote][b]Ola L Martins-329921 (11/17/2011)[/b][hr]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.[/quote]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)[/quote]Interesting side-fact: BETWEEN is not valid in a filtered index definition; you have to expand the condition into >= and <= tests.

RE: DATETIME Puzzle

L' Eomot Inversé — Mon, 21 Nov 2011 04:41:54 GMT

[quote][b]Thomas Abraham (11/17/2011)[/b][hr]After Che Guevara's question from yesterday, I'll take the easy two points. [/quote]Drat! I'll have to change my avatar to something less recognisable! :crying: Either that or change my nickname to "El comandante" so that it matches the avatar. :w00t:Edit: I changed it. Still unmatched, though.

RE: DATETIME Puzzle

L' Eomot Inversé — Mon, 21 Nov 2011 04:35:05 GMT

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 [quote] I am extending Dwayne Dibley's question to show how 'Time' part in date is considered by SQL server[/quote]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].

RE: DATETIME Puzzle

Hugo Kornelis — Fri, 18 Nov 2011 02:39:31 GMT

[quote][b]GPO (11/17/2011)[/b][hr]I would have written it[code="sql"]SELECT *FROM #DateTestWHERE SampleDate >= @DATE1 AND SampleDate < (@DATE2 + 1)[/code][/quote]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.

RE: DATETIME Puzzle

Ola L Martins-329921 — Fri, 18 Nov 2011 02:37:00 GMT

[quote][b]Hugo Kornelis (11/17/2011)[/b][hr][quote][b]Ola L Martins-329921 (11/17/2011)[/b][hr][quote][b]GPO (11/17/2011)[/b][hr]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.[/quote]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...)[/quote]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'[/quote]2 × :blush: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...

RE: DATETIME Puzzle

GPO — Thu, 17 Nov 2011 18:43:39 GMT

[quote]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'[/quote]Massive thanks Hugo for putting this much more elegantly than I. Sorry to everyone else:blush:

RE: DATETIME Puzzle

GPO — Thu, 17 Nov 2011 18:21:02 GMT

If I were to write the query (which was )[code="sql"]SELECT *FROM #DateTestWHERE SampleDate BETWEEN @DATE1 AND @DATE2[/code]I would have written it[code="sql"]SELECT *FROM #DateTestWHERE SampleDate >= @DATE1 AND SampleDate < (@DATE2 + 1)[/code]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.

RE: DATETIME Puzzle

SQLRNNR — Thu, 17 Nov 2011 15:40:51 GMT

[quote][b]GilaMonster (11/17/2011)[/b][hr][quote][b]Ola L Martins-329921 (11/17/2011)[/b][hr]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.[/quote]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)[/quote]I have seen this myth propagated from time to time (use lt and gt in lieu of between).

RE: DATETIME Puzzle

SanDroid — Thu, 17 Nov 2011 15:02:26 GMT

[quote][b]GPO (11/17/2011)[/b][hr]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.[/quote]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.

RE: DATETIME Puzzle

GilaMonster — Thu, 17 Nov 2011 14:58:19 GMT

[quote][b]Ola L Martins-329921 (11/17/2011)[/b][hr]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.[/quote]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)

RE: DATETIME Puzzle

Hugo Kornelis — Thu, 17 Nov 2011 13:16:42 GMT

[quote][b]Ola L Martins-329921 (11/17/2011)[/b][hr][quote][b]GPO (11/17/2011)[/b][hr]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.[/quote]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...)[/quote]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'

RE: DATETIME Puzzle

Ola L Martins-329921 — Thu, 17 Nov 2011 12:49:31 GMT

[quote][b]GPO (11/17/2011)[/b][hr]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.[/quote]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...)

RE: DATETIME Puzzle

GPO — Thu, 17 Nov 2011 10:56:26 GMT

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.

RE: DATETIME Puzzle

OzYbOi d(-_-)b — Thu, 17 Nov 2011 09:58:24 GMT

nice easy question today - tks

RE: DATETIME Puzzle

rfr.ferrari — Thu, 17 Nov 2011 09:20:32 GMT

good question!!!

RE: DATETIME Puzzle

Revenant — Thu, 17 Nov 2011 09:14:36 GMT

THis was a really simple one - thanks!

RE: DATETIME Puzzle

Ola L Martins-329921 — Thu, 17 Nov 2011 08:22:36 GMT

[quote][b]sknox (11/17/2011)[/b][hr]I've seen this crop up enough time on the forums to see why it rates 2 points.While the answer is super-easy if you know DATETIME, this question highlights one of the easiest mistakes for a beginner to make, and one that has serious real-world implications.[/quote]Hear! Hear!

RE: DATETIME Puzzle

Daniel Bowlin — Thu, 17 Nov 2011 08:13:14 GMT

Good question, thanks.

RE: DATETIME Puzzle

Toreador — Thu, 17 Nov 2011 08:11:49 GMT

A very easy question, unless you temporarily forget how to count :blush:

RE: DATETIME Puzzle

vk-kirov — Thu, 17 Nov 2011 07:50:45 GMT

[quote][b]tabinsc (11/17/2011)[/b][hr]I didn't see anything as to what question we were supposed to answer. Luckily I guessed correctly that we were supposed to select how many rows were returned by the query.[/quote]The question is written in large letters, although with a typo ("may" instead of "many") :-)[quote][b]How may rows will be returned by following query?[/b][/quote]

RE: DATETIME Puzzle

tabinsc — Thu, 17 Nov 2011 07:16:53 GMT

I didn't see anything as to what question we were supposed to answer. Luckily I guessed correctly that we were supposed to select how many rows were returned by the query.

RE: DATETIME Puzzle

getoffmyfoot — Thu, 17 Nov 2011 07:15:59 GMT

Good Question. Was just working with this scenario...

RE: DATETIME Puzzle

sknox — Thu, 17 Nov 2011 06:31:46 GMT

I've seen this crop up enough time on the forums to see why it rates 2 points.While the answer is super-easy if you know DATETIME, this question highlights one of the easiest mistakes for a beginner to make, and one that has serious real-world implications.

RE: DATETIME Puzzle

Mattrick — Thu, 17 Nov 2011 06:03:44 GMT

Good question with a good explanation the presents a learning experience for those who got tripped up on either the dates or the BETWEEN. Thanks,Matt

RE: DATETIME Puzzle

Britt Cluff — Thu, 17 Nov 2011 05:50:22 GMT

This wasn't totally easy. You really need to focus on the time part of the question. Thanks for submitting.

RE: DATETIME Puzzle

Thomas Abraham — Thu, 17 Nov 2011 05:30:16 GMT

[quote][b]Koen Verbeeck (11/17/2011)[/b][hr]Good question, but a bit easy for 2 points.[/quote]After Che Guevara's question from yesterday, I'll take the easy two points. "I know you are here to kill me. Shoot, coward, you are only going to kill a man."

RE: DATETIME Puzzle

SQLRNNR — Thu, 17 Nov 2011 05:09:10 GMT

Thanks for the question.

RE: DATETIME Puzzle

53280comma1 — Thu, 17 Nov 2011 01:29:31 GMT

change your language settings and you Could get 0

RE: DATETIME Puzzle

palotaiarpad — Thu, 17 Nov 2011 00:34:11 GMT

2 points for so an easy question? Nice! :-D

RE: DATETIME Puzzle

Koen Verbeeck — Thu, 17 Nov 2011 00:00:53 GMT

Good question, but a bit easy for 2 points.

DATETIME Puzzle

Sunil Chandurkar — Wed, 16 Nov 2011 21:35:14 GMT

Comments posted to this topic are about the item [B]DATETIME Puzzle[/B]