SQLConfiguration SQLConfiguration


Server Interaction  «Prev 

Date Time Functions in SQL-Server

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