SQL SERVER – Puzzle with Miliseconds – Win USD 50 Amazon Gift Card

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:

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)

Previous Post
SQL SERVER – Looking Inside SQL Complete – Advantages of Intellisense Features
Next Post
SQL SERVER – T-SQL Window Function Framing and Performance – Notes from the Field #103

Related Posts

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.

  • Shiv Mishra
    May 27, 2016 3:34 pm

    the reason behind difference is (.) and (:)

  • Alpesh Patel
    May 27, 2016 4:26 pm

    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

  • akhtarshahbaz1hahbaz Akhtar
    May 27, 2016 5:10 pm

    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

  • madanuprashanth
    May 27, 2016 10:00 pm

    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 .

  • Deepak Patil
    May 31, 2016 2:47 pm

    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.

  • mansish kumar singh
    August 20, 2016 5:22 pm

    Difference between dateformat only
    YYYY-MM-DD HH:MI:SS(24h)

  • –The Difference is
    –FirstVal like this

    –SecondVal like this

    –Dot is the Different

  • SELECT CAST (‘2017-1-13 23:59:55:2’ AS DATETIME) what is logic behind answer? please anybody explain?


Leave a Reply Cancel reply

Exit mobile version