# 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:

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?

### How to Participate?

• Leave an answer for contest 1 in the comment section of the blog.
• 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)

## How to Trim TIME Part in DATETIME Values? – Interview Question of the Week #200

• 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 (:)

• 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

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)
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)