SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7419 Visits: 3395
Comments posted to this topic are about the item T-SQL

I run on tuttopodismo
Sree Veerendra Kumar
Sree Veerendra Kumar
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 36
If we execute the query with given data
SELECT [InsertedOn]
FROM MyLogs
WHERE [InsertedOn] BETWEEN '20090131' AND '20090131 23:59:59:999'
The output is as follows...

2009-01-31 00:00:00.000
2009-01-31 00:01:01.000
2009-01-31 12:59:59.127
2009-01-31 00:01:01.000
2009-01-31 13:00:00.997
2009-01-31 00:01:01.127
2009-02-01 00:00:00.000
2009-01-31 00:01:01.000
2009-01-31 21:59:58.347
2009-01-31 23:59:59.997
2009-01-31 00:01:01.000
2009-01-31 21:59:58.347
2009-02-01 00:00:00.000
2009-01-31 00:01:01.000
2009-01-31 22:59:58.347
2009-01-31 09:01:30.347
2009-02-01 00:00:00.000

with the above result the answer for the given questions will contain the value 2009-02-01 00:00:00.000.....

will not contain the following value
2009-01-30 00:00:00.000
Robbert Hof
Robbert Hof
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1602 Visits: 834
I have the same experience, no 2009-01-30 00:00:00:000 in the result set.
According to the explanation this is correct behaviour.

Nice excersise though, this was new to me.

Robbert



Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7419 Visits: 3395
The correct answer is only ONE:
2009-02-01 00:00:00:000, 2009-01-31 00:01:01:000, 2009-01-31 21:59:58:347
I do not know why there was a multiple choise.

The author of QotD.

I run on tuttopodismo
hakan.winther
hakan.winther
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3301 Visits: 612
The answer is incorrect since 2009-01-30 00:00:00:000 will not be returned. But you are pointing out a very important issue of SQL Server, datetime values are rounded to increments of .000, .003, or .007 seconds even in insert/update statements.

If you execute
SELECT InsertedOn FROM MyLogs 

you will see will see that '2009-01-31 23:59:59:999' are converted to '2009-02-01 00:00:00:000' when they were inserted.

If you use SQL Server 2008, there is a new datatype datetime2 with a precision of 100 nanoseconds. And if you try the same sample with datetime2 datatype you will end up with the correct result. But in the meantime, my recommendation is to not use BETWEEN in SQL Server 2005 for datetime values because of this side effects.


Håkan Winther
Senior development DBA

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18429 Visits: 12426
I both liked and disliked this question.

The reason I like it, is that it points out a very common misconception when querying for intervals - I have lost track of how often I have seen people try to query for a one-day interval using some variation of the code in this question, instead of the correct way as demonstrated in the answer.

The reasons for not liking it are less significant, but there are more so they add up.

1) The way the answers were presented made it very hard to see exactly what they were. Each answer had two or three datetimes, often not in chronological order - the numbers started dancing before my eyes and I almost missed that one of the answers had a day number 30 instead of 31. I think it would have been easier to present one datetime value per answer options, arranged chronologically. After all, the QotD is about testing SQL Server skills, not skills in reading lots on similar looking numbers...

2) The "correct" answer lists four datetime values. There is no direct correspondence to any of the given answer options. I guess that the "correct" answer is the UNION of two or more of the given options, but after staring at these numbers to choose my answer, I frankly couldn't be bothered to repeat the exercise.

3) The "correct" answer is not correct at all. There is no way that 2009-01-30 00:00:00.000 can ever be returned by this query.

4) I saw only one answer that I thought to be completely correct. But the QotD permitted multiple answers, and when I submitted my own questions, I found no way to mark a question as "tick all that apply" without marking at least two answers as correct, so I assumed there had to be a second "correct" answer. The only one that wasn't obviously wrong was the first one, figuring that the author himself had accidentally missed that 2009-01-31 12:59:59:128 woukd be rounded to ~.127. All other answers were more wrong (in my eyes).

5) It's really a shame that the explanation in the answer focuses entirely on the ending numbers that can be returned (maybe that made Steve think the answer including Jan 30 should have been marked as correct as well?). A very important point here, maybe more important than the possible ending number, is that the datetime used in the query will also be rounded to one of those ending numbers - and in this case, the rounding will be UP, not down (rounding to the nearest value), so that feb 1st, midnight exactly, will be included in the results. This is what has taken many people br surprise, and should in my eyes have been the biggest takeaway of this question. Not whether SQL Server can return a datetime with a time part of 12:37:53.128.


6) Technically, all answers were wrong. SQL Server uses a period to seperate milliseconds from seconds, not a colon as shown in the answers.


All in all, I applaud the author for the idea of writing a QotD about the way datetimes are rounded and how a filter for a time interval should or shouldn't be constructed, but I am very disappointed that the many small errors made this a missed opportunity.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Adi Cohn
Adi Cohn
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7881 Visits: 6594
I think that the correct answer is the answer that shows those dates:

2009-02-01 00:00:00:000, 2009-01-31 00:01:01:000, 2009-01-31 21:59:58:347

All the dates are in the time frame that was in the query and all of them have the correct numbers in the milliseconds part.

For some reason the answer that was said to be correct was
2009-01-30 00:00:00:000, 2009-01-31 00:01:01:127, 2009-02-01 00:00:00:000, 2009-01-31 00:01:01:000, 2009-01-31 21:59:58:347. This couldn't be the correct answer because the date 2009-01-30 00:00:00:000 is not in the criteria that was specified in the query.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1935 Visits: 1249
ok, so based on the question editor's comment above, this QOTD is broken - I answered the only one that contained only valid values (the one starting with "2009-02-01 00:00:00:000"), and got it "wrong".

I am happy to have learned something though - I did not know that DateTime milliseconds always ended in 0, 3 or 7; I knew there was rounding to the order of 3 milliseconds or so, but I did not know it was always consistently at those marks.

Thanks for an extra tidbit!

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Andy Reilly
Andy Reilly
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1678 Visits: 405
The principle of the question was good just the layout was poor as mentioned above and the answer given is obviously wrong - surprise that one person actually got this right. How about having a question like this what numbers am I thinking of 1, 2, 3, 4, 5, 6, 7, 8, 9, 0 - We'd have as much chance of getting it correct :-) BTW I was thinking of 359 any other answers were wrong Smile
brewmanz
brewmanz
Right there with Babe
Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)

Group: General Forum Members
Points: 757 Visits: 406
So .. do we get a bonus point because the QotD author got it wrong?
Oh, I just did by posting!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search