SQL Teaser: Datetime (pt 2) 1

[kaht]

select convert(datetime, '2007-01-01 00:00:00.001')
select convert(datetime, '1970-01-01 00:00:00.002')
select convert(datetime, '1970-01-01 00:00:00.003')
select convert(datetime, '1970-01-01 00:00:00.004')
select convert(datetime, '1970-01-01 00:00:00.005')
select convert(datetime, '1970-01-01 00:00:00.006')
select convert(datetime, '1970-01-01 00:00:00.007')
select convert(datetime, '1970-01-01 00:00:00.008')
select convert(datetime, '1970-01-01 00:00:00.009')
select convert(datetime, '1970-01-01 00:00:00.010')

What will be the millisecond value for each of these select statements?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[SQLDenis]

and for fun add 1 and 2 milliseconds

SELECT DATEADD(ms,1,CONVERT(datetime, '2007-01-01 00:00:00.001'))
SELECT DATEADD(ms,2,CONVERT(datetime, '2007-01-01 00:00:00.001'))

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[kaht]

heh, I see I did 2007 for my first date and 1970 for the rest - sooo consistent

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[kaht]

Surely the javascript punk didn't post a teaser too hard for you all.....

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[SQLDenis]

You gave the answer away in the first datetime teaser from today

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Lamprey13]

Spoiler

2007-01-01 00:00:00.000
1970-01-01 00:00:00.003
1970-01-01 00:00:00.003
1970-01-01 00:00:00.003
1970-01-01 00:00:00.007
1970-01-01 00:00:00.007
1970-01-01 00:00:00.007
1970-01-01 00:00:00.007
1970-01-01 00:00:00.010
1970-01-01 00:00:00.010

 

[kaht]

Not really - the logic is in the first teaser but that would insinuate all the milliseconds would increment by .03

.000
.000
.003
.003
.003
.006
.006
.006
.009
.009
.
.012
.
.
.015
.
.
etc.....

When I typed the SQL code into QA interestingly this was not the case which is why I posted the "teaser"

Lamprey - I'm sorry I didn't specify - you're NOT supposed to just type it into QA and post the results. Besides, I just asked for the millisecond values

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[SQLDenis]

now guess why you get 7 instead of 6 for the third value?
if a tick = 0.003 then 2 ticks should be 0.006 not 0.007 right?
I know the answer but do you know why this is like that

000
003
[b]007[/b]
010

010
013
[b]017[/b]
020

020
023
[b]027[/b]
030

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

Here is the answer

Spoiler

because of rounding

0.333
0.666
0.999

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[kaht]

I haven't read the answer, but I'll guess that it's because

Spoiler

it's not really .003, but .0033333333333, which means it's really .0066666666 which rounds to .007, same with .0099999999 rounding to .010

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[ESquared]

If it was as simple as "just rounding" then .005 would round to what... .003 or .007?

or .00333333333 or .006666666?


[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[AlexCuse]

kaht, I think there was a teaser on this subject a couple weeks ago (the 3/7/10 milliseconds). But maybe you didn't notice, 'cause you are a javascript punk and all ;-)

Ignorance of certain subjects is a great part of wisdom