What is basic difference between different date data types in sql server 2012. In SQL 2012, there are new date datatypes have been included such as Datetime2, DateTimeOffset, smalldatetime, Time, Date.
Major difference between each is date time precision and storage space. So, Let look by changing one date data type to other how much space we could save. And saving more space that also means when we index, retrieve or filter data using where condition it also have performance benefits.
The best way to find out how much memory being using by datatype is to create table with datatypes and check the column description using SP_helptext [table_name].
create table tblDate (dt1 datetime, dt2 datetime2,
t1 time, d1 date, dtoff datetimeoffset, sdt smalldatetime)
Select Table name and press Alt+F1 or use sp_helptext tblDate.
It’s clear from the data filed length that if you want to store just date then use DATE field and if your looking to store time then go for SMALLDATETIME, for little more sophesticated datetime then just follow the conventional DATETIME.
But if you are looking for precision then it’s advise to use DATETIME2(7) use it with precision so that you are not over burdening the server to store more precise values.
DATETIMEOFFSET is the most expensive date datatype in sql 2012, having said that it might be more appropriate to use when your application is dealing with multiple time zones.
I also found this link which gives you max date value supported by sql server.