Date and time functions enable a programmer to get the system date, as well as to manipulate the date and time values stored in the database.
Because date and time functions are useful in many different circumstances, it is difficult to emphasize one particular usage as being more essential than the others. One complaint that you might hear is that SQL Server does not allow storing only the date or only the time's .
You must store both 1) date and 2) time in the same column if you use the DATETIME or SMALLDATETIME data type.
Of course, you have the alternative of storing date values as strings, as in '1/1/2003'.
Another alternative is to use the DATETIME data type and then use one of the date and time functions to retrieve only the needed portion (date or time) from the table.
| Data type |
Format |
Range |
Accuracy |
Storage size (bytes) |
User-defined fractional second precision |
Time zone offset |
| time |
hh:mm:ss[.nnnnnnn] |
00:00:00.0000000 through 23:59:59.9999999 |
100 nanoseconds |
3 to 5 |
Yes |
No |
|
YYYY-MM-DD |
0001-01-01 through 9999-12-31 |
1 day |
3 |
No |
No |
| smalldatetime |
YYYY-MM-DD hh:mm:ss |
1900-01-01 through 2079-06-06 |
1 minute |
4 |
No |
No |
| datetime |
YYYY-MM-DD hh:mm:ss[.nnn] |
1753-01-01 through 9999-12-31 |
0.00333 second |
8 |
No |
No |
| datetime2 |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] |
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 |
100 nanoseconds |
6 to 8 |
Yes |
No |
| datetimeoffset |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm |
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) |
100 nanoseconds |
8 to 10 |
Yes |
Yes |