After years of controversy, Microsoft finally cut the date-time data type, adding 4 new date-time data types in one breath in the new version of SQL Server 2008, including:
- Date: A pure date data type.
- Time: A pure temporal data type.
- DateTime2: A new datetime type that mentions precision to 100 nanoseconds.
- DateTimeOffset: The new datetime type, which adds the time zone section based on the DateTime2.
The following is a simple summary table of datetime data types in SQL Server 2008:
| Data type |
format |
range of Values |
accuracy |
Storage size |
| Date |
Yyyy-mm-dd |
0001-1-1
9999-12-31 |
1 days |
3 bytes |
| Time |
hh:mm:ss.nnnnnn |
0:0:0.000000
23:59:59.999999 |
100 nanoseconds |
3-5 bytes |
| smalldatetime |
Yyyy-mm-dd
Hh:mm:ss |
1900-1-1
2079-6-6 |
1 minutes |
4 bytes |
| Datetime |
Yyyy-mm-dd
hh:mm:ss:nnn |
1753-1-1
9999-12-31 |
0.00333 seconds |
8 bytes |
| DateTime2 |
Yyyy-mm-dd
hh:mm:ss:nnnnnn |
0001-1-1
9999-12-31 |
100 nanoseconds |
6-8 bytes |
| DateTimeOffset |
Yyyy-mm-dd
hh:mm:ss:nnnnnn
+|-hh:mm |
0001-1-1
9999-12-31
(Global Standard Time) |
100 nanoseconds |
8-10 bytes |
To use these data types, SQL Server 2008 also introduces a series of T-SQL functions.
Three functions for obtaining high-precision system time ( because these three functions are all operating system time, so the accuracy can only reach 10 milliseconds ):
- Sysdatetime: Returns the local time of the server running the instance of SQL Server, the data type is datetime2 (7) and does not contain time zone information.
- Sysdatetimeoffset: Returns the local time zone information for the server running the instance of SQL Server with the data type DateTimeOffset (7).
- Sysutcdatetime: Returns the standard world time of the server running the instance of SQL Server with the data type DateTime2 (7).
Functions for the time zone conversion:
- Switchoffset (DateTimeOffset, time_zone): Returns data for a specific time zone based on the input world time and time zone information, such as Switchoffset (' 2008-1-1 0:0:0 + 8:00 ', ' -07:00 ') The return value will be ' 2007-12-31 9:00-07:00 ', so we know that our New Year's Day time is only 9:00 in the morning. ( There is an interesting situation is the Switchoffset function Time_zone parameter hours of the leading 0 o'clock can not be omitted, the example we just used if the Time_zone parameter written ' 7:00 ' will be an error, must be written ' 07:00 ', but DateTimeOffset data in the time zone part of the leading 0 o'clock can be omitted, that is, ' 2008-1-1 0:0:0 + 8:00 ' and ' 2008-1-1 0:0:0 + 08:00 ' are acceptable, for the time zone in the minute part is also the case. However, we recommend that you develop a good coding habits, all leading 0 do not omit. )
- Todatetimeoffset (datetime, offset): Returns a world time value based on the date-time parameter value entered and the time zone parameter value. For example Todatetimeoffset (' 2008-1-1 0:0:0 ', ' +08:00 ') The return value is ' 2008-1-1 0:0:0 + 08:00 '.
By the way, list the date-time functions that are already available in SQL Server 2005, but do not introduce them:
- function to get system time: Current_timestamp,getdate,getutcdate
- Returns the specified part of the datetime: Datename,datepart,day,month,year
- Functions for calculating datetime differences: DATEDIFF, DATEADD ( It is important to note that datetime, smalldatetime data types support the + and-operators, but for date, time, DateTime2, DateTimeOffset is not supported. )
- function to calculate date time: DATEADD
- function to set date time display format: @ @DATEFIRST, set Datefirst,set dateformat,@ @LANGUAGE, set Language,sp_helplanguage
- function to confirm datetime data format: ISDATE
SQL Server 2008 Improvements to date-time types
