DateTime DataTypes

Before SQL 2008 there are 2 Datetime Data type to store Date and Time. These are:

1. 1.   DateTime
2. 2.   SmallDateTime

You cannot store Data and Time separately. You had to separate Date and Time using convert Function.

In SQL Server 2008 four new datetime data types are introduced:

1. DATE
2. TIME
3. DATETIME2 
4. DATETIMEOFFSET


1 – DATE data type
• Allows to store a date without time
• By default date is displayed in YYYY-MM-DD format
• The Date datatype will accept DATETIME values and implicitly convert it to a DATE by removing the time portion
• Each date variable requires 3 bytes of storage and precision of 10 digits
• Range for the DATE datatype is from 0001-01-01 through 9999-12-31.
• In SQL Server 2005 the minimum date is 1st Jan 1753 and in SQL Server 2008 it is 1st Jan 0001.

2- Time data type
• To store only the time
This will display the time:
DECLARE @dt as TIME
SET @dt=getdate()
PRINT @dt 
• Is based on 24 hour clock
• Range for TIME datatype is 00:00:00:0000000 through 23:59:59:9999999

3 – DATETIME2 data type
• Is a date/time datatype with larger fractional seconds and year range that the existing DATETIME datatype 
• Fraction can be specified
• Maximum fraction that can be specified is 7 while the minimum fraction is 0
DECLARE @dt7 DATETIME2(7)
SET @dt7=getdate()
PRINT @dt7

4- DATETIMEOFFSET data type
• DATETIMEOFFSET defines a date that is combined with a time of a day that has time zone awareness and is based on a 24 hour clock.
• Currently (in SQL Server 2005) when saving the date and time in a column, it will not indicate what time zone that date and time belongs to.
• DECLARE @dt DATETIMEOFFSET(0)
SET @dt=‘2007-10-29 22:50:55 -1:00’
DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1=‘2007-10-29 22:50:55 +5:00’
SELECT DATEDIFF (hh,@dt,@dt1)
This will give the difference in hours between the two dates specified.
The pattern followed will be @dt1-@dt.