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
The column “cast(‘2015-01-01 14:48:34.69’ as datetime)” says it wants 69% of a second as indicated by the dot(‘.’). The second column says it wants 69 milliseconds out of 999 (approx 1000 milliseconds in a second) as indicated by the colon (‘:’).
The reason that the millisecond part is different is because value one has a period before the millisecond part whereas the second value has the correct colon before the millisecond part. The period is causing the cast to make the 69 as value.
because you put . (dot) in first value so it is considering 690 millisecond in FirstVal hence it is not rounding, and you put : (column) in second value so it is considering 69 millisecond in SecondVal hence it is rounding to 070 (SQL DateTime is accurate to 3 milliseconds).
I am from INDIA. my answer is – because you put .(dot) in first value, so it is considering 690 millisecond hence it is not rounding, and you put : (column) in second value, so it is considering 69 millisecond hence it is rounding to 070.
FirstVal is treated as an incomplete milliseconds value and a zero is added to the least significant digit. SecondVal is treated as a numeric and stored with a leading zero added to the milliseconds value. However SQL Server datetime values are rounded to increments of .000, .003, or .007 seconds as shown by the SecondVal result.
USA
Milliseconds can be preceded by either a colon (:) or a period (.). If a colon is used, the number means thousandths-of-a-second. If a period is used, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates 20 and one-thousandth seconds past 12:30; 12:30:20.1 indicates 20 and one-tenth seconds past 12:30.
the reason behind difference is (.) and (:)
In the first string I found (.)Operator with 2 digit.so it will add the 0 as postfix in the end and will give result as ‘2015-01-01 14:48:34.690’
While in second string I Found(:) Operator with 2 digit so it will 0 as prefix and will give result as ‘2015-01-01 14:48:34.069’
In this result,we get result as 34.690 and 34.069 because Milliseconds take 3 digits In datetime Format
If you use a dot as a separator and if the number of digits is less than three, the number is left justified and missing digits are filled with 0 on the right side. So 69 becomes 690
If you use a colon as a separator and if the number of digits is less than three, the number is right justified and missing digits are filled with 0 on the left side. So 69 becomes 069
FirstVal has ‘.’ 34.69, SecondVal has ‘:’. FirstVal is in correct DateTime format thats why output is same as you supplied.. SecondVal coverts the value to DateTime and then rounds it because of ‘:’
Such a tricky question. I enjoyed it too much.
It is all about. (Dot) and : (Colon)
The original format is: YYYYMMDD hh:mm:ss[.mmm] that means using .(Dot) for millisecond section.
We specify each element in the format. This also includes the T, the colons (:), and the period (.)
When we use:
CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) FirstVal,
We are using original format. As a result, output is .69
However, by using : we say how many sections out of 1000(as its range is between 0 and 999) is used.
When we use :69, it means 69 out of 1000 that will be converted to .0690 .
In this While executing Select statement it process on one field and then by on second field so it makes a difference of mili second differance…
SQL DB reads / process one by one so in between time is the gap of processing time takes column to column….
In this example date format used in two format one contains (:) in between second and millisecond and other contain (.) in between second and millisecond . And convert in single format. In first example it will take 1000 millisecond for 1 second ,and for second it will take 100 millisecond for 1 second. that is why wile converting in date time (:69) will show 69/1000 =070 and (.69) will show 69/100 =.69
SQl Date Format
YYYY-MM-DD HH:MI:SS(24h) is ODBC Canonical
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h) is ODBC Canonical (with nanoseconds)
It just because of DOT and Colon. For DOT it is 690(part of 1000ms) and for Colon it is 69/1000. If you will put .069 and :69 the same result will come.
Difference between dateformat only
YYYY-MM-DD HH:MI:SS(24h)
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)
–The Difference is
–FirstVal like this
–34.69
–SecondVal like this
–34:69
–Dot is the Different
SELECT CAST (‘2017-1-13 23:59:55:2’ AS DATETIME) what is logic behind answer? please anybody explain?