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
Short answer: You actually do not have the same value selected.
Long answer:
First, let me state a fact about DATETIME in SQL Server. The accuracy of DATETIME is 1/333 of a second so numbers that do not divide precisely get rounded. Values get rounded to increments of .000, .003, or .007 seconds
FirstVal has a value of 34.69 seconds. There are no milliseconds specified, only partial seconds. Note how this is different than SecondVal which explicitly has a value of 69 in the milliseconds (one has a period before the 69, the other has a semicolon).
For FirstVal, when SQL does the CAST, it converts 34.69 seconds to 34 seconds and 690 milliseconds since 690 is in an increment of .000 seconds.
For SecondVal, when SQL does the CAST, it converts 34 seconds and 69 milliseconds to 34 seconds and 70 milliseconds since 69 is not an increment of .000, .003, or .007 seconds and 70 milliseconds is the closest value that SQL server rounds to.
Source: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017
T-sql one: It has to do with datetime types. I speak from memories… I remember that the old datetime had some kind of rounding at milliseconds (one in three… it’s related to the number of bytes that holds datetime) whereas datetime2 doesn’t . There is only one difference… One is with . And one is with : so I can speculate that one is translating to datetime2 first and than datetime and one straight as datetime. Is just a hitch… Without looking up in the docs
Correct me if i am wrong but i think SQL Server is only accurate to 1/300th of a second. It will round values to the nearest 1/300th. This is the reason the 2nd select statements value is getting rounded.
Correct me if i am wrong but i think SQL Server is only accurate to 1/300th of a second. It will round values to the nearest 1/300th, that is why the value of the second select statement is getting rounded of.
Hi, I am from the USA.
Correct me if i am wrong but i think SQL Server is only accurate to 1/300th of a second. It will round values to the nearest 1/300th, that is why the value of the second select statement is getting rounded of.
.69 means 690 thousands where :69 means 069 thousands. DateTime will round 069 off to 070 because it rounds it off to 000 or 003 or 007.
.69 means 690 thousands while :69 means 069 thousands and datetime will round 069 to 070.
The reason for the difference in output is the difference in the input :-D
The delimiter between seconds and milliseconds is for the first column a (correct) decimal point (.), whereas the second column uses an incorrect colon (:).
The reason for the difference in output is the difference in the input :-D
The delimiter between seconds and milliseconds is for the first column a (correct) decimal point (.), whereas the second column uses an incorrect colon (:).
This would be due to the fact that : is forcing sql to add a zero infront of the 69, making 9 the smallest value. As far as i know this when converting would only be 0, 3, 7.
The difference is one is using a colon and the other is using a period. The period would specify an exact time to the thousandths of a millisecond using the decimal system, ie .69 = .690. When using the colon it specifies that it is 69 milliseconds or when it rounds up, like it tends to do, it displays as .070. SO the answer is one specifies an exact time where the other specifies how many milliseconds has passed between seconds.
In the FirstVal, what is being specified is a fraction of a second: “34.69” seconds – which is the same as 34 seconds and 690 milliseconds.
In the SecondVal, what is being specified is milliseconds (thousandth of a second): “34:69”, so we have 34 seconds and 69 milliseconds.
As a followup:
The reason that the second value is .070 instead of .069 is that the accuracy of DATETIME is 3.33 milliseconds. https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017
[email removed] Mexico
Because colon(:) means thousandths of a second and (.) by every digit (max 7) has a precision, tenths of a second, hundredths of a second, thousandths of a second etc.
The difference in the millisecond part is because, DATETIME has a precision of 3.3 milliseconds and is accurate to 3.3 milliseconds. DATETIME milliseconds precision can be achieved using DATETIME2 datatype.
DATETIME values are rounded to increments of .000, .003, or .007 seconds
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017
In above select statement 34.69 and 34:69 makes the difference.
This is because of rounding of datetime fractional second precision (see: .
If you use DATETIME2 the resluts will be the same.
I’m from the USA.
Consider format
a) yyyy-mm-dd hh:mm:ss.SSS
b) yyyy-mm-dd hh:mm:ss:SSS
Focus on a) .SSS
b) :SSS
If I maintain above format either with . or : format for SSS(Milisecond),It gives me appropriate answer.
But when I prefer below format then it makes difference. How? Let me explain.
—
a) .SS
As we know by mathematical rule after (.) we can add 0 for fraction value,but not before.
EX: 0.56 = .5600 .056
— 2015-01-01 14:48:34.69 results 2015-01-01 14:48:34.690 –(First value)
b):SS
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) SecondVal
Here ssms takes the value as 070 (why not 069? I will explain below in details) .
If we write according to format like
2015-01-01 14:48:34:690
then it will give correct result.
Here are some of query which can clear more
SELECT
CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) FirstVal,
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) SecondVal
SELECT
CAST(‘2015-01-01 14:48:34.069’ AS DATETIME) FirstVal, –yyyy-mm-dd hh:mm:ss.SSS
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) SecondVal –yyyy-mm-dd hh:mm:ss:SS
SELECT
CAST(‘2015-01-01 14:48:34.069’ AS DATETIME) FirstVal, –yyyy-mm-dd hh:mm:ss.SSS
CAST(‘2015-01-01 14:48:34:690’ AS DATETIME) SecondVal –yyyy-mm-dd hh:mm:ss:SSS
If we want same result,then query should be…
SELECT
CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) FirstVal, –yyyy-mm-dd hh:mm:ss.SS
CAST(‘2015-01-01 14:48:34:690’ AS DATETIME) SecondVal –yyyy-mm-dd hh:mm:ss:SS
How Miliseconds works?
.SS to .SSS
.11 .010
.12 .013
.13 .013
.14 .013
.15 .017
.16 .017
.17 .017
.18 .017
.19 .020
.20 .020
.21 .023
similar sequence will be continue.
INDIA
I am from INDIA
“.69” means 69/100, because it was in milisecond actually it means 690/1000 but “:69” means 69/1000
SELECT
CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) FirstVal,
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) FirstVal
–In first one -> 34.69 -> it is 0.69 seconds i.e. 690 milliseconds
–In seconds one -? 34:69 -> it is 34 seconds and 69 milliseconds i.e. 69 milliseconds
— Sql results displays milliseconds in seconds decimal terms only hence 69 milliseconds is converted to .070