2

I'm using SQL Server 2008 R2.

Using SQL Server Management Studio, I've created a database, and a new table.

In the table I have 2 columns with data types Time(0) and Datetime2(0).

enter image description here

I've inserted a record in this table with values :

12:50:34...........2015-02-02 12:50:34

But when this record is saved , the values are like below :

12:50:34...........2015-02-02 12:50:34.0000000

This is the image inside SQl Server management studio : enter image description here

So the Time(0) column's value remain as I have typed. But the Datetime2(0) column's value has those 0 at the end.

Why does the DateTime2(0) column's value change?

alex
  • 694
  • 3
  • 15
  • 35

1 Answers1

0

Datetime2(0) means the fractional precision is 0.

I think when SQL Server stores a date in type of datetime2 then read that data, thinks that it has a datetime2 with 7 precision for millisecond then truncating it to n or 0 in this case:

For example:

DECLARE @d1 datetime2 = CAST('1968-10-23 12:45:37.1237' AS datetime2(0))
        @d2 datetime(0) =  '1968-10-23 12:45:37.1237';

SELECT @d1, @d2;

result will be

1968-10-23 12:45:37.0000000     1968-10-23 12:45:37

This (result) is same for time(0):

DECLARE @t1 time =  CAST('1968-10-23 12:45:37.1237' AS datetime2(0)),
    @t2 time(0) =  '1968-10-23 12:45:37.1237';

SELECT @t1, @t2

So, When you use datetime(0) and time(0) as type of your columns the behavior is same:

DECLARE @t table(dt2 datetime2(0), t time(0));

INSERT INTO @t VALUES ('1968-10-23 12:45:37.1237', '1968-10-23 12:45:37.1237');

SELECT * FROM @t;

result is:

dt2                 t
1968-10-23 12:45:37 12:45:37
shA.t
  • 16,580
  • 5
  • 54
  • 111
  • My question was why in the case of Time(0) , the extra 0 at the end are not added , but on DateTime2(0) these 0 ate the end remain ? I have a case when the time on both values have no milliseconds. – alex Jul 04 '15 at 13:02
  • @alex as I test and add to the answer result of `time(0)` and `datetime2(0)` are same and without any trailing `0` ;). – shA.t Jul 04 '15 at 13:06
  • @ shA.t Please look at the picture on my updated question.This image was generated using : Edit top 200 Rows. – alex Jul 04 '15 at 13:10
  • Can you also add your table structure (like create statement) and your query for that result ;). – shA.t Jul 04 '15 at 13:14
  • @ shA.t I've added the table structure. I repeat that to generate that table with values , I've used Edit top 200 Rows from Sql server Management Studio. – alex Jul 04 '15 at 13:17
  • @alex, the formatting of values is entirely up to SSMS so it seems the "edit top n rows" function doesn't consider the defined datetime2 precision for display purposes. The value in the database is constrained according to the specified precision though. – Dan Guzman Jul 04 '15 at 13:45
  • @Dan - So do you confirm that the value is stored without milliseconds ? ( because this is my goal ) And what about the size of this field , is 6 bytes or ??? – alex Jul 04 '15 at 13:49
  • @alex, yes, I can confirm the value is stored according to the specified data type definition (6 bytes here). SSMS just isn't considering the defined precision for editing and display. If you do enter a fractional precision, it will be rounded to the nearest second. – Dan Guzman Jul 04 '15 at 14:00