- Initializing and Incrementing Variables
- Inserting Multiple Rows with a Single Statement
- Merging Multiple Data Sets
- Separate Date and Time Data Types
- New Date- and Time-Related Functions
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Separate Date and Time Data Types
SQL Server now supports new data types for storing date values separately from time values. Previously we were limited to DATETIME or SMALLDATETIME data types, both of which required storing both date and time in the same column. If we didn't specify either date or time, SQL Server would use midnight as the default time value and January 1, 1900 as the default date.
Starting with SQL Server 2008, we can use the DATE data type to store just dates from January 1, 0001 to December 31, 9999. The DATE data type is appropriate for storing values for which time isn't relevant; for example, date of hire or birthdate. This data type makes it much easier to search for rows that have a specific date value when we don't know the exact time value. Using previous versions of the software, we would have to convert the DATETIME column into a string and then compare the result with a specific date, or use the DATEPART function to extract month, day, and year from a specific column and compare those with the respective date parts of a date value. For example, let's say we wanted to check whether a date and time variable had the value of July 1, 2011. The following wouldn't work:
/* presume we're running these commands on July 1, 2011, in the United States */
DECLARE @date DATETIME = GETDATE()
IF @date= '07-01-2011'
BEGIN
SELECT 'match found'
END
ELSE
SELECT '@date doesn''t match 07-01-2011'
We have some options to work around the DATETIME data type's limitation; the easiest alternative is to convert the value to VARCHAR data type. The following statements would find the match:
DECLARE @date DATETIME = GETDATE()
IF CONVERT(VARCHAR(12), @date, 110)= '07-01-2011'
BEGIN
SELECT 'match found'
END
Using the DATE data type, we no longer need to make the effort of converting the variable to VARCHAR and having to know that date format 110 will return the value in MM-DD-YYYY format before we compare it to a given date. Instead, we can find a match as follows:
DECLARE @date DATE = GETDATE()
IF @date = '07-01-2011'
BEGIN
SELECT 'match found'
END
More importantly, comparing a DATETIME column to a date value by converting it first prevented SQL Server from using an index on this column, thereby causing slower performance.
The TIME data type stores only the time values ranging from 00:00:00:0000000 to 23:59:59:9999999. As you can tell from the supported range, you can use the TIME data type to store fractions of a second with greater precision than allowed by the DATETIME data type, which only supports three digits for storing fractions of a second. The TIME data type is appropriate for storing values for which date is irrelevant; for example, business opening and closing hours.
SQL Server 2008 also introduced the DATETIME2 data type, which is an extension of the DATETIME type, but allows seven digits to store fractions of a second with an accuracy of 100 nanoseconds. DATETIME2 also has a greater date range than DATETIME, supporting the same range of dates as the DATE data type.
Having separate date and time data types saves space: The DATE data type requires 3 bytes, whereas DATETIME requires 8 bytes. SMALLDATETIME only requires 4 bytes, but it doesn't support a wide date range, as the DATE data type does. If you have millions of rows in your database, a difference of 5 bytes per row (8 - 3 =5 bytes) can yield significant storage savings. Leaner data type columns also translate into leaner indexes and improved query performance.
Another new data type, DATETIMEOFFSET, stores date and time values and is aware of time zones. DATETIMEOFFSET supports the same date range as DATE and allows the same sub-second precision as TIME. Additionally, you can specify the time zone difference from Coordinated Universal Time (abbreviated as UTC). The time difference from UTC can range from -14 to +14 hours, and it must be expressed as two-digit hours and two-digit minutes. The DATETIMEOFFSET data type uses eight to ten bytes per row, depending on the required precision, and has the default value of 1900-01-01 00:00:00 00:00. This data type is best for global applications where it's essential to know the time when the transaction occurred, adjusted for location where the transaction took place.
The following example illustrates the usage of the DATETIMEOFFSET data type with the DATEDIFF function to find the time difference between two dates, one reported in UTC and another in Eastern Standard Time (EST):
DECLARE @date1 DATETIMEOFFSET = '07-01-2011 06:00:00 -00:00' DECLARE @date2 DATETIMEOFFSET = '07-01-2011 05:00:00 -05:00' SELECT DATEDIFF(HOUR, @date1, @date2) AS difference_in_hours
Result:
|
difference_in_hours |
|
4 |