Last contest, which we had ran was in May and it had received amazing responses. I once again reached out to kind team of Embarcadero and they agreed to support the next contest. The contest has two steps and they are very simple. It took me a while to build contest, but it is a really fun one. I am very confident that once you try out the contest, you will love it.
Two Giveaways:
(USA) USD 50 Amazon Gift Card to 1 Individual
(India) INR 2500 Amazon.in Gift Card to 1 Individual
(Rest of the world) USD 50 Amazon Gift Card to 1 Individual
Contest 1: T-SQL
Run following T-SQL script and observe the answer:
SELECT
CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) FirstVal,
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) SecondVal
When we look at the answer, there is a difference between the milliseconds part in the resutlset, whereas you can notice that in the SELECT statement I have specified different milliseconds part. The question is why there is a difference in the millisecond part even though I have same value selected?
Contest 2: Download and Install DBArtisan
This is the easy part of the contest. You just have to download and install DBArtisan. You can download DBArtisan from here.
How to Participate?
- Leave an answer for contest 1 in the comment section of the blog.
- Leave a comment with the same email address which you have used to download DBArtisan.
- The contest is open till December 1st, 2015 Midnight PST.
- The winner will be announced on December 10th, 2015.
- There will be three winners 1) USA 2)Â India 3) Rest of the World
- Please do not forget to indicate which country do you belong to qualify for the contest.
- All the correct answer to the contest will be hidden till the winner is announced.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
64 Comments. Leave new
2 issues at play. First is datetime datatype is accurate to 3 ms. Second is the string literal format for a datetime can be either 00:00:00:00 or where 00:00:00.00. You specified it both ways, but they mean different things. The argument could be made with all of this that it’s more “standard” to use datetime2.
Milliseconds takes 3 digits
1)if we use (.) operator with 2 digit then it will add the 0 in the end.
and it will become .690
2)if we use (:) operator with 2 digit then it will add the 0 in the first and it will become .069 but sql server stores the time in .003,.007 and .000 interval so finally it will become .069+.001=.070
[email removed]
Rahul Bansal-India
Hello Pinal,
I am just trying to identify for your first question in My sql 2008 R2 version. But both value are same.
Here is my output.,
SELECT CAST(‘2015-01-01 14:48:59.69’ AS DATETIME) FIRSTVAL, CAST(‘2015-01-01 14:48:59.69’ AS DATETIME) SECONDVAL
FIRSTVAL SECONDVAL 2015-01-01 14:48:59.690 2015-01-01 14:48:59.690
Please tell me how could you generate this difference.
thanks Sagar Mistry
Notice the difference .69 vs :69 in FirstVal and SecondVal respectively.
In the second instance you have swapped ‘ : ‘ with ‘ . ‘
Something wrong in contest open and close date…
SELECT
CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) FirstVal, –ISO 8601 format
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) SecondVal –SQL Server datetime format
AS per ISO 8601 format .69 = .690 (decimal method)
but in SQL Server datetime format :69 = :069 (:mmm milisecond method)
–>–So here because of conversion according to format, we are getting a different result…
CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) –> CAST(‘yyyy-mm-dd hh:mi:ss.mmm’ AS DATETIME) –> CAST(‘2015-01-01 14:48:34.690’ AS DATETIME) –> 2015-01-01 14:48:34.690
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) –> CAST(‘yyyy-mm-dd hh:mi:ss:mmm’ AS DATETIME) –> CAST(‘2015-01-01 14:48:34:069’ AS DATETIME) –> 2015-01-01 14:48:34.070
–>– But if we pass 3 digit value then no conversion is not required and it will give a similar result as below…
CAST(‘2015-01-01 14:48:34.690’ AS DATETIME) –> CAST(‘yyyy-mm-dd hh:mi:ss.mmm’ AS DATETIME) –> CAST(‘2015-01-01 14:48:34.690’ AS DATETIME) –> 2015-01-01 14:48:34.690
CAST(‘2015-01-01 14:48:34:690’ AS DATETIME) –> CAST(‘yyyy-mm-dd hh:mi:ss:mmm’ AS DATETIME) –> CAST(‘2015-01-01 14:48:34:690’ AS DATETIME) –> 2015-01-01 14:48:34.690
datetime has accuracy of 3.33 milisecond and
example.
34.5 – it means sql take as 34.500 mili second
34:5 – it means sql take as 34.005 mili second
position of 5 will be depends upon what we choose 34.5 or 34:5
DateTime datatype in sql gives date accuracy of one three-hundredth of a second and values are rounded to increments of .000,.003 or .007
Therefore, CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) this statement results in rounding the millisecond value just by adding an extra zero at the end. While because second statement CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) separates milliseconds by ‘:’ instead of by default separator ‘.’ it rounds it to next increment of milliseconds.
Because the fraction part for the seconds values are always rounded to increments of .000, .003, or .007 seconds.
There are two different timestamp values used here for the conversion. One has 34.69 seconds i.e 34690 milliseconds and other is 34 seconds and 69 milliseconds i.e 34069 milliseconds. – Anurag(India)
Hey, i think you made a typo with the dates for the competition! unless we have a time machine :)
It seems you wrote blog today and declare contest date of October month.
“The contest is open till October 15th, 2015 Midnight PST.
The winner will be announced on October 20th, 2015.”
first of all we need to keep remember that
“datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.” (MSDN source)
FirstVal .69 is two digit part equivalent of .690 with ‘dot’ decimal separator – means 69 of 100 or 690 of 1000 (second parts)
in SecondVal :69 with colon ‘:’ is equal to .069 – means there was exactly 69 miliseconds
and in datetime type .069 has been rounded to .070
The formats are different Pinal, and hence the difference. The correct syntax would be to specify the milliseconds after a . and not a : (colon)
Run time delay between the first select and second select
because when the query got executed it executed the first statemnt before the second statement.That made the difference in milliseconds of the second result because it got changed when it reached to second query.
HI, it states comp end on 15 October. Isthiscorrect
Contest 1:
FirstVal uses 34.69 (decimal point) to signify 69/100 of a sec.
SecondValues 34:69 (semi colon) to signify 69 milliseconds.
Country: UK, rest of the world.
Best answer
When milliseconds are preceded by a colon it represents thousandths of a second, not a decimal like when it is preceded by a period. Also, your contest dates are in the past, conversion issue? :)
OOPS! forgot to mention I was in USA.
Milliseconds preceded by a colon indicate thousandths of a second, whereas a period indicates tenths of second. Since SQL Server only calculates datetime accuracy to about 1/300th of a second, the .069 is rounded up to .070.