SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / DATETIME 2 / Latest Posts

RE: DATETIME 2

SQLRNNR — Mon, 06 Aug 2012 00:44:51 GMT

Thanks Ron. This question made me think a bit.

RE: DATETIME 2

vips8nov — Sun, 05 Aug 2012 11:42:57 GMT

Hugo,You answered my question! THANKS A LOT! :-)wanna follow u n ur posts! (dun get me wrong ;-))

RE: DATETIME 2

Hugo Kornelis — Sun, 05 Aug 2012 07:09:17 GMT

[quote][b]L' Eomot Inversé (8/5/2012)[/b][hr]But there is a minor error in the entence I've quoted: "at the very first microsecond" should be "in the very first 20th of a microsecond" (because 4 places precision is accurate to 1/10 or a millisecond, and the fact that times are rounded means the overrun from the boundary only half of that. (and of course the same 20th for microsecond).[/quote]:rolleyes: I *knew* someone would call me out on that.I decided to simplfy this because my explanation was already approaching novelette length. And I thought it would be interesting to see how long it would take for someone to correect me, and who it would be. ;-)

RE: DATETIME 2

L' Eomot Inversé — Sun, 05 Aug 2012 06:00:43 GMT

[quote][b]Hugo Kornelis (8/4/2012)[/b][hr]Or in human people languages, all orders placed at any time on dates from July 1st up to and including July 30, plus orders placed at the very first microsecond (or millisecond for the T4 table) of July 31.[/quote]Very clear explanation, Hugo. I thinkl it will help a lot of people. But there is a minor error in the entence I've quoted: "at the very first microsecond" should be "in the very first 20th of a microsecond" (because 4 places precision is accurate to 1/10 or a millisecond, and the fact that times are rounded means the overrun from the boundary only half of that. (and of course the same 20th for microsecond).

RE: DATETIME 2

Hugo Kornelis — Sat, 04 Aug 2012 15:22:33 GMT

The question is great. But I don't really like the presentation, for two reasons:1. The order of presentation - putting the SELECT before the CREATE TABLE and INSERT statements adds confusion that serves no apparent purpose.2. The layout of the INSERT statements. My first thought was that there was a copy/paste error, because I saw three INSERT VALUES statements and there were answer options that mentioned four rows returned. I had to look very careful to see the trick. Using either six INSERT statements or one single INSERT statement would have been a lot clearer and would have allowed me to focus on the actual subject being tested.[quote][b]vips8nov (8/4/2012)[/b][hr]I did not get it. as i am new. please let me know why dis has happned and which rows would be displayed for each?i also went thru the msdn link but din get much understanding.[/quote]I hope you don't mind if I answer this?There are two possibly confusing issues in this question. One is about precision of datetime2 values, the other about interpretation of the BETWEEN.Let's look at the precision first. The following datetime values are inserted in both tables:1. June 30, 23:59:59.99999992. July 30, 15:32:00.00000003. July 30, 23:59:59.99999994. July 31, 0:00:00.00000005. July 31, 23:59:59.99999996. August 1, 0:00:00.0000000Table D7 has the OrderDate column defined as datetime2(7), which means 7 positions of fractional seconds are retained (this is also the default for datetime2, if I recall correctly). All values given use 7 fractional positions, so all values are stored as given.Table D4 however has OrderDate defined as datetime2(4). Only 4 fractional positions are retained, so the values given are rounded (rounding instead of truncating is a design choice that is probably documented somewhere in BOL - I'm just too lazy to hunt down a link now). So values 1, 3, and 5 are rounded up to respectively July 1st, 0:00:00; July 31st, 0:00:00; and August 1st, 0:00:00.The queries used include WHERE OrderDate BETWEEN '20110701' AND '20110731'. A human reader would interpret this as the entire month of July, 2011 (**). But SQL Server is not a human reader. In SQL Server, a date without time portion is assumed to be 0:00:00 of that date. And BETWEEN means that the boundary values are included, so this WHERE clause is synonym to WHERE OrderDate >= '20110701 0:00:00' AND OrderDate <= '20110731 0:00:00' (with the appropriate number of zeroes for fractional seconds). Or in human people languages, all orders placed at any time on dates from July 1st up to and including July 30, plus orders placed at the very first microsecond (or millisecond for the T4 table) of July 31.For the D7 table, rows 2, 3, and 4 match this. Row 1 is before 20110701 0:00:00; row 5 and later are after 20110731 0:00:00.For the D4 table, rows 1, 2, 3, and 4 match. Row 1 now is included, because the time is rounded up to fall in the interval; rows 2, 4 and 6 are not affected by the rounding; and row 3 is rounded up from just before the end of the interval to exactly the end of the interval (so it is still included). Row 5 is rounded up as well, but it already was a day (minus a fraction of a second) late, so that doesn't affect the results.(**) Since getting all data for a specific month is a common requirement that is very often coded wrong, here is the only truly safe way to find all orders that are placed inthe month of July 31 (regardless of time):WHERE OrderDate >= '20110701' AND OrderDate < '20110801'You can use BETWEEN, but it is awkward because you have to specify the upper limit of the period with the exact right number of fractional seconds, depending on data type used, and it's dangeroud because you have to remember change it when the data type is ever changed. You can also use date functions such as MONTH or date conversion functions, but that would severly reduce the usefulness of any index on the datatime column.

RE: DATETIME 2

vips8nov — Sat, 04 Aug 2012 13:36:41 GMT

Hi Ron,I did not get it. as i am new. please let me know why dis has happned and which rows would be displayed for each?i also went thru the msdn link but din get much understanding.would be grateful.

RE: DATETIME 2

Stewart "Arturius" Campbell — Fri, 03 Aug 2012 03:05:03 GMT

Learned something new here, thanks, Ron

RE: DATETIME 2

Sreepathi1987 — Thu, 02 Aug 2012 22:00:25 GMT

Great....................!:-P

RE: DATETIME 2

Sreepathi1987 — Thu, 02 Aug 2012 21:59:24 GMT

Great................!:-P

RE: DATETIME 2

L' Eomot Inversé — Thu, 02 Aug 2012 16:26:23 GMT

[quote][b]L' Eomot Inversé (8/2/2012)[/b][hr][quote][b]patrickmcginnis59 (8/2/2012)[/b][hr][quote][b]L' Eomot Inversé (8/2/2012)[/b][hr][quote][b]patrickmcginnis59 (8/2/2012)[/b][hr]Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.[/quote]In that case I hope you are really good at working out what test cases to use![/quote]As opposed to what? Not testing?[/quote]Not as opposed to anything.But if you are using code "in a project", it's very unlikely that you can rely on testing as opposed to working out what's happening, to get it right. Of course it would be pretty stupid to try to get by without testing (indeed pretty thorough testing) , but relying on testing to absolve you from writng code based on the requirements and understanding why that code might fit the requirements doesn't seem to me to be a sensible or useful approach.Testing, design verification, understanding what some code does, and other things like using formal methods (eg Z) to verify correctness are not opposed to each other; they are all needed. What surprised me was the idea that just testing (as opposed to trying to understand before testing) was the right approach in a project.[/quote]PatrickI think I probably misinterpreted both your original post and your response to my reply to that. Looking at it when not hurried makes it look quite different. I don't think you were advocating testing without trying to understand what was happening, and I apology for suggesting that you might be.

RE: DATETIME 2

L' Eomot Inversé — Thu, 02 Aug 2012 11:02:10 GMT

[quote][b]patrickmcginnis59 (8/2/2012)[/b][hr][quote][b]L' Eomot Inversé (8/2/2012)[/b][hr][quote][b]patrickmcginnis59 (8/2/2012)[/b][hr]Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.[/quote]In that case I hope you are really good at working out what test cases to use![/quote]As opposed to what? Not testing?[/quote]Not as opposed to anything.But if you are using code "in a project", it's very unlikely that you can rely on testing as opposed to working out what's happening, to get it right. Of course it would be pretty stupid to try to get by without testing (indeed pretty thorough testing) , but relying on testing to absolve you from writng code based on the requirements and understanding why that code might fit the requirements doesn't seem to me to be a sensible or useful approach.Testing, design verification, understanding what some code does, and other things like using formal methods (eg Z) to verify correctness are not opposed to each other; they are all needed. What surprised me was the idea that just testing (as opposed to trying to understand before testing) was the right approach in a project.

RE: DATETIME 2

patrickmcginnis59 — Thu, 02 Aug 2012 10:25:54 GMT

[quote][b]L' Eomot Inversé (8/2/2012)[/b][hr][quote][b]patrickmcginnis59 (8/2/2012)[/b][hr]Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.[/quote]In that case I hope you are really good at working out what test cases to use![/quote]As opposed to what? Not testing?

RE: DATETIME 2

Revenant — Thu, 02 Aug 2012 10:23:21 GMT

Nice, very nice question. Thanks, Ron!

RE: DATETIME 2

L' Eomot Inversé — Thu, 02 Aug 2012 10:18:35 GMT

[quote][b]patrickmcginnis59 (8/2/2012)[/b][hr]Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.[/quote]In that case I hope you are really good at working out what test cases to use!

RE: DATETIME 2

Cliff Jones — Thu, 02 Aug 2012 10:12:45 GMT

[quote][b]paul.knibbs (8/2/2012)[/b][hr]I didn't know about the selectable precision in datetime2, but once I read up about it, it became fairly clear what the answer should be--the only thing I was unsure about was if the value would get truncated or rounded up; I guessed the right way, fortunately![/quote]+1 Good question, I have not yet had the occasion to use the Datetime2 data type.

RE: DATETIME 2

Thomas Abraham — Thu, 02 Aug 2012 09:35:07 GMT

[quote][b]mtassin (8/2/2012)[/b][hr]Thanks for making me think... I had to sit there and keep reminding myself how the datetime2 values would round and then tried to keep two mental counts (statement 1 and statement 2) in my head as I did the rounding...:) glad I got it right, and glad I didn't have to use more than the fingers on one hand for each count :)[/quote]Definitely would have saved time to run the code, but I too did it this way. Good thing it was later in the day than usual, and I was on my nth coffee. Thanks bitbucket!

RE: DATETIME 2

Rob Schripsema — Thu, 02 Aug 2012 09:23:16 GMT

Great question!

RE: DATETIME 2

paul.knibbs — Thu, 02 Aug 2012 08:54:18 GMT

I didn't know about the selectable precision in datetime2, but once I read up about it, it became fairly clear what the answer should be--the only thing I was unsure about was if the value would get truncated or rounded up; I guessed the right way, fortunately!

RE: DATETIME 2

KWymore — Thu, 02 Aug 2012 08:38:26 GMT

Nice question. I can definitely see a potential for issues like this when you have multiple developers working on the same database.

RE: DATETIME 2

bkmsmith — Thu, 02 Aug 2012 07:59:33 GMT

[quote][b]patrickmcginnis59 (8/2/2012)[/b][hr][quote][b]bitbucket-25253 (8/2/2012)[/b][hr]I am rather surprised at the low percentage of correct answers.[center][b]Correct answers: 37% (76)[/b] Incorrect answers: 63% (129) Total attempts: 205 [/center]And the implications this could have to those designing a DB, and inadvertently or deliberately using different definitions for a date time object in different tables (based only on each individual table's requirements) and then, when selecting, wondering why Select statements using a join / case / where on a date column do not return the correct data.[/quote]Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.[/quote]+1Patrick, you are not the only one answering the QOTD this way. I also missed this one today.Thanks for the question Bitbucket, and the reinforcement of the nuances of DateTime comparison.-------------Brian Smith

RE: DATETIME 2

Tock — Thu, 02 Aug 2012 07:58:27 GMT

A most excellent question for content AND presentation.+1Cheers,Steve

RE: DATETIME 2

roryp 96873 — Thu, 02 Aug 2012 07:43:39 GMT

[quote][b]patrickmcginnis59 (8/2/2012)[/b][hr][quote][b]bitbucket-25253 (8/2/2012)[/b][hr]I am rather surprised at the low percentage of correct answers.[center][b]Correct answers: 37% (76)[/b] Incorrect answers: 63% (129) Total attempts: 205 [/center]And the implications this could have to those designing a DB, and inadvertently or deliberately using different definitions for a date time object in different tables (based only on each individual table's requirements) and then, when selecting, wondering why Select statements using a join / case / where on a date column do not return the correct data.[/quote]Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.[/quote]But, but, but... the points you're missing out on... :-P

RE: DATETIME 2

patrickmcginnis59 — Thu, 02 Aug 2012 07:13:18 GMT

[quote][b]bitbucket-25253 (8/2/2012)[/b][hr]I am rather surprised at the low percentage of correct answers.[center][b]Correct answers: 37% (76)[/b] Incorrect answers: 63% (129) Total attempts: 205 [/center]And the implications this could have to those designing a DB, and inadvertently or deliberately using different definitions for a date time object in different tables (based only on each individual table's requirements) and then, when selecting, wondering why Select statements using a join / case / where on a date column do not return the correct data.[/quote]Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.

RE: DATETIME 2

sestell1 — Thu, 02 Aug 2012 07:12:25 GMT

Nice question!

RE: DATETIME 2

Luis Cazares — Thu, 02 Aug 2012 07:01:59 GMT

I'm really proud of me because instead of running the query, I went to SQL help to find out what where the 4 and 7 in the datetime2 declaration.I've learned something new in the correct way: putting attention, investigating, thinking and, only then, answering.;-)

RE: DATETIME 2

mtassin — Thu, 02 Aug 2012 06:55:41 GMT

Thanks for making me think... I had to sit there and keep reminding myself how the datetime2 values would round and then tried to keep two mental counts (statement 1 and statement 2) in my head as I did the rounding...:) glad I got it right, and glad I didn't have to use more than the fingers on one hand for each count :)

RE: DATETIME 2

bitbucket-25253 — Thu, 02 Aug 2012 06:09:49 GMT

I am rather surprised at the low percentage of correct answers.[center][b]Correct answers: 37% (76)[/b] Incorrect answers: 63% (129) Total attempts: 205 [/center]And the implications this could have to those designing a DB, and inadvertently or deliberately using different definitions for a date time object in different tables (based only on each individual table's requirements) and then, when selecting, wondering why Select statements using a join / case / where on a date column do not return the correct data.

RE: DATETIME 2

(Bob Brown) — Thu, 02 Aug 2012 04:29:12 GMT

Excellent question. I honestly had to run the code to get the correct answer but got a lesson on datetime2.

RE: DATETIME 2

L' Eomot Inversé — Thu, 02 Aug 2012 04:24:56 GMT

Nice question. But 23:59:59.9999999 is rather easy to round to 100 microseconds, isn't it. So I'm surprised so many have it wrong.

RE: DATETIME 2

Stuart Davies — Thu, 02 Aug 2012 03:57:08 GMT

Got me thinking - good question.Thanks

RE: DATETIME 2

skanker — Thu, 02 Aug 2012 01:41:10 GMT

Nice question - should have read it closer as I misunderstood the insert part of the question...doh...coffee time.

RE: DATETIME 2

Koen Verbeeck — Thu, 02 Aug 2012 01:39:07 GMT

[quote][b]khelloufsofiane 6183 (8/2/2012)[/b][hr]Hi,??????????How can I choose an answer and there is statement that insert into D4 table. !!!!!!!!!!!!!!!!!!!!!!!!!!Thanks[/quote]Read the question. Read it again. Read it again and now pay special attention to the sentence enclosed in brackets.

RE: DATETIME 2

D.Oc — Thu, 02 Aug 2012 01:37:40 GMT

Good question Ron,Thanks!

RE: DATETIME 2

khelloufsofiane 6183 — Thu, 02 Aug 2012 01:35:24 GMT

Hi,??????????How can I choose an answer and there is statement that insert into D4 table. !!!!!!!!!!!!!!!!!!!!!!!!!!Thanks

RE: DATETIME 2

Lokesh Vij — Thu, 02 Aug 2012 01:27:33 GMT

Wonderful question!Such questions build your concepts...and keep you think while you solve them.Keep posting such questions :-)

RE: DATETIME 2

BrainDonor — Thu, 02 Aug 2012 01:26:20 GMT

Excellent question Ron. I had to run the code through to understand what it was doing and learned something new.

RE: DATETIME 2

Koen Verbeeck — Thu, 02 Aug 2012 00:18:38 GMT

Thanks, great question.I first thought: "statement 1 doesn't return a row at all!" Then I read the question a little better :-D

RE: DATETIME 2

WayneS — Wed, 01 Aug 2012 21:36:48 GMT

Thanks Ron... I love questions that make me think.

DATETIME 2

bitbucket-25253 — Wed, 01 Aug 2012 21:26:22 GMT

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