Using Time(0) and Datetime2(0) in SQL Server

Question

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).

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 :

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?

Answer 1

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