Date : This data type holds only date in the variables. This data type will not hold the time values.
Size of the date data type is : 3 Bytes
Format for Date data type is: YYYY-MM-DD
Default value for this data type is : 1900-01-01
Date data type can hold the values from 0001-01-01 to 9999-12-31
Time:
This data type holds the time data till 100nano seconds accuracy.
Syntax for Time data type is :
Time(N)
(OR)
Time
N = Precision (Indicates number of digits after seconds)
Format for the Time Data type with precision 7 is: HH:MM:SS.NNNNNNN
N can vary from 0 to 7
That is Time data type can store the time from 00:00:00.0000000 through 23:59:59.9999999
Size of Time data type is: 5 Bytes
Smalldatetime:
Smalldatetime data type cannot hold the time to milliseconds precison. This data type can hold only time to seconds level.
That is Format for Smalldatetime data type is: YYYY-MM-DD HH:MM:SS
This data type requires 4 bytes of space.
Minimum date that Smalldatetime data type can hold is : 1900-01-01
Maximum that Smalldatetime data type can hold is : 2079-12-31
This data type is not frequently used since the range of the date values it can hold is very limited.
Datetime:
This is most frequently used data type to hold the date and time related data.
Format of the Datetime data type is: YYYY-MM-DD HH:MM:SS.NNN
Minimum and maximum dates that can be accommodated in datetime field is 1/1/1753 and 12/31/9999
Default value for the datetime field is 1900-01-01 00:00:00.000
Size of Datetime data type is 8Bytes.
Datetime2:
Datetiem2 data type is extension for Datetime data type.
Datetime data type will hold only 3 digits after seconds. That is it can hold the time value only till 1000milliseconds precision.
Where as Datetime2 can hold the time to 100nanoseconds precision. This data type will be used when time needs to captured at nanoseconds precision level.
Format for Datetime2 data type is: YYYY-MM-DD HH:MM:SS.NNNNNNN
Syntax for Datetiem2 data type is: Datetime2(N)
N = Precision. This indicates the number of digits after dot(decimal point) in time part.
Size of Datetime2 data type varies based on the Precision value.
If N < 3, then Datetime2 data type used 6 Bytes
If N= 3,4 ; then Datetime2 data type uses 7 Bytes of Space
If N>4, then Datetime2 data type uses 8 Bytes of Space
N value can vary from 0 to 7.
Default value for N is 7.
Minimum and Maximum dates that Datetime2 can hold is 1st Jan, 1753 and 31st Dec, 9999
Datetimeoffset:
Datetimeoffset data type can hold the time value till 100nanoseconds accuracy.
Datetimeoffset have a special feature which differentiates this data type from other date and time data types. That is Datetimeoffset data type will hold the time zone indicators along with the data and time.
Format for Datetimeoffset data type is :
YYYY-MM-DD HH:MM:SS.NNNNNNN <+/-> hh:mm
Example:
Indian standard time 5hrs 24mins 42 sec 9876542 milliseconds on 1st Jan, 2012 will be displayed as :
2012-01-01 05:24:42.9876542 +05:30
Datetimeoffset data type uses 10Bytes of space.
