Millisecond values and separator in time values

When you express millisecond values in a time, you need to make sure that it has three digits or use dot as a millisecond separator.

Consider the following example

declare @date datetime
set @date='20101201 14:23:12.77'
select @date as date

The result is

date
-----------------------
2010-12-01 14:23:12.770

Now use the same code as below

declare @date datetime
set @date='20101201 14:23:12:77'
select @date as date

The result is
date
-----------------------
2010-12-01 14:23:12.077

Did you notice the change in the millisecond value. In the first example it is 770 and in second example it is 077?

It is because in the first example dot (.) is used as a millisecond separator whereas in the second example colon (:) is used as a millisecond separator. SQL Server always make the number of digits of milliseconds into 3.

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 77 becomes 770

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 77 becomes 077

So you need to be aware of this and as a best practice always use a dot as a separator for millisecond.

Advertisements

One thought on “Millisecond values and separator in time values

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s