What are differences between date and time data types in SQL Server Transact-SQL? I want to compare of date and time data types?

Here is a comparison table of all date and time data types supported in SQL Server Transact-SQL:

Data type         Storage size Precision   Default Format /
                  (bytes)                  Value range
----------------- ------------ ----------- ------------------------------------

TIME(s)           3 to 5       0.0000001 s hh:mm:ss.nnnnnnn              
Local timezone                             00:00:00.0000000 to 
                                           23:59:59.9999999

DATE              3            1 day       YYYY-MM-DD                    
Local timezone                             0001-01-01 to 
                                           9999-12-31         

SMALLDATETIME     4            1 minute    YYYY-MM-DD hh:mm:ss           
Local timezone                             1900-01-01 to
                                           2079-06-06

DATETIME          8            0.00333 s   YYYY-MM-DD hh:mm:ss.nnn       
Local timezone                             1753-01-01 to
                                           9999-12-31

DATETIME2(s)      6 to 8       0.0000001 s YYYY-MM-DD hh:mm:ss.nnnnnnn   
Local timezone                             0001-01-01 00:00:00.0000000 to
                                           9999-12-31 23:59:59.9999999

DATETIMEOFFSET(s) 8 to 10      0.0000001 s YYYY-MM-DD hh:mm:ss.nnnnnnn +|-hh:mm
timezone offset                            0001-01-01 00:00:00.0000000 to
                                           9999-12-31 23:59:59.9999999 (in UTC)

Here are some simple rules on selecting date and time data types;

• If you want to store the most complete and most accurate date and time information, use DATETIMEOFFSET(7). Of course, DATETIMEOFFSET(7) is the most expensive data type in processing time.
• If you want to store the most accurate time only information, use TIME(7).
• If you want to store date and time for normal applications, use DATETIME.

 

⇒Date/Time Operations and Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials