How DATETIME2 differs with DATETIME?

If you are having a table with two different columns of datetime datatype as FirstModified and LastModified. And you have datatype for FirstModified as Datetime and LastModified as Datetime2.

create table testDemoForDateDatatype

(

firstName varchar(50),

firstModified datetime,

lastModified datetime2,

)

Now when you are populating each of them with SYSDATETIME, you assume that the value inserted in the table will be the same, you have done on insert operation on it, no update operation has yet done on the table.

insert into testDemoForDateDatatype values('abc', sysdatetime() ,sysdatetime ())

insert into testDemoForDateDatatype values('xyz', sysdatetime (),sysdatetime ())

insert into testDemoForDateDatatype values('def', sysdatetime (),sysdatetime ())

But when you querying the table with distinct clause on both these columns and you will be surprised by the result because your perception will be like both the column will have the same data, but in fact, they had very different data.

The date value in the DATETIME field gets rounded up whereas the value didn’t round up in the field having DATETIME2 datatype.

*The best way is to use GETDATE () if you are using DATETIME datatype.

*And SYSDATETIME () if you are using DATETIME2

 select getdate() -- 2011-12-28 11:23:39.727

select sysdatetime () --2011-12-28 11:23:39.9062500

NOTE: DATETIME2 and Sysdatetime() was introduced in SQL Server 2008. So try this in 2008 SQL Server.

Post Reference: Vikram Aristocratic Elfin Share