Date and Time data type in SQL Server 2008

Hi,

Today let's discuss about Date & Time data type introduced in SQL Server 2008.

We always had to do extra steps to remove time from Age field or DOB field because we don't need time. Also while comparing 2 dates, we need to remove Time so that we get correct data.

All this extra work or steps will not be required because SQL Server 2008 has introduced 4 new (DATE RELATED) data types :

DATE

TIME

DATETIME2

DATETIMEOFFSET

All these data types separate date and time, support larger date range, improved accuracy and support for timezone. DATE data type stores only Date, TIME data type stored only time. DATETIME2 data type is an improved version of Datetime data type, which is improved to provide better accuracy and a larger date range. DATETIMEOFFSET data type adds time zone component in the date.

Let's check the size of each data type:

Date: Storage in bytes = 3 Accuracy = 1 day
Time: Storage in bytes = 3-5 Accuracy = 100 nanoseconds
Datetime2: Storage in bytes = 6-8 Accuracy = 100 nanoseconds
DateTimeOffSet: Storage in bytes = 8-10 Accuracy = 100 nanoseconds

I would like to highlight one point in this:
The date format is 'YYYY-MM-DD' for all the data types but because date format is language dependent, it is possible when you change the language the format is also changed.

DATE Data Type

Date Only
01-01-0001 to 31-12-9999

TIME Data Type

Time Only, Variable Precision - 0 to 7 decimal places for seconds To 100 nanoseconds

DATETIME2 Data Type

01-01-0001 to 31-12-9999
Variable Precision - to 100 nanoseconds, a datetime type w/ larger fractional seconds and year range than the existing DATETIME type

DATETIMEOFFSET

01-01-0001 to 31-12-9999
Variable Precision - to 100 nanoseconds
Time Zone Offset (From UTCTime) Preserved
Not Time Zone Aware - No Daylight Saving Time Support

The three new types that contain a time component (TIME, DATETIME2, and DATETIMEOFFSET) enable you to specify fractional seconds precision in parentheses following the type name. The default is 7, meaning 100 nanoseconds. If you need a fractional second accuracy of milliseconds, such as three for example, you must explicitly specify it: DATETIME2(3).

declare @e datetime2(3)
set @e=GETDATE()
select @e
------------------------------
2009-07-17 11:28:44.090
------------------------------

declare @d datetime2(5)
set @d=GETDATE()
select @d

------------------------------
2009-07-17 11:28:44.09000
------------------------------

declare @f datetime2(7)
set @f=GETDATE()
select @f

------------------------------
2009-07-17 11:28:44.0900000
------------------------------

The precision changes as per the specified size.

In the sys.columns view, the precision attribute describes the total number of characters in the default literal string representation of the value, and the scale describes the number of digits in the fractional part of the seconds. In INFORMATION_SCHEMA.COLUMNS, the DATETIME_PRECISION attribute describes the number of digits in the fractional part of the seconds.

Let's look at examples of these:

DECLARE
@d AS DATE = '2009-02-12',
@t AS TIME = '12:30:15.1234567',
@dt2 AS DATETIME2 = '2009-02-12 12:30:15.1234567',
@dto AS DATETIMEOFFSET = '2009-02-12 12:30:15.1234567 +02:00';

SELECT @d AS [@d], @t AS [@t], @dt2 AS [@dt2], @dto AS [@dto];

Here is the output:
@d = 2009-02-12
@t = 12:30:15.1234567
@dt2 = 2009-02-12 12:30:15.1234567
@dto = 2009-02-12 12:30:15.1234567 +02:00

Functions associated with Date data types:

To support the new date and time data types, SQL Server 2008 introduces new functions and enhanced existing functions. The new functions are SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET.

SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET return the current system date and time value.
SYSDATETIME returns the current date and time as a DATETIME2 value,
SYSUTCDATETIME returns the current date and time in UTC as a DATETIME2 value, and SYSDATETIMEOFFSET returns the current date and time along with the system time zone as a DATETIMEOFFSET value.

To get only the current date or only the current time, you can cast the value returned from the SYSUTCDATETIME function to DATE or TIME, as the following example shows:

SELECT
CAST(SYSDATETIME() AS DATE) AS [current_date],
CAST(SYSDATETIME() AS TIME) AS [current_time];

current_date current_time
2009-07-17 11:12:24.9059210

NOTE: When You convert a datetime datatype to DATE, SQL Server query optimizer still relies on index ordering to process the query more efficiently. This is contrary to the usual behavior, where conversion of a column to a different type prevents the optimizer from relying on index order. The new behavior means that the optimizer might consider an index seek for a query filter that has a conversion to DATE. For example, the plan for the following query performs an index seek on the index on the CurrencyRateDate DATETIME column:

USE AdventureWorks;
SELECT FromCurrencyCode, ToCurrencyCode, EndOfDayRate
FROM Sales.CurrencyRate
WHERE CAST(CurrencyRateDate AS DATE) = '20040701';

FromCurrencyCode ToCurrencyCode EndOfDayRate
USD ARS 3.5501
USD AUD 1.7763
USD BRL 3.6949
USD CAD 1.5758
USD CNY 8.2872
USD EUR 0.9962
USD GBP 0.6343
USD JPY 120.586
USD MXN 10.255
USD SAR 3.7541
USD USD 1.00
USD VEB 1362.0302

The structure of the table is:
Column_name Type
ScaleCurrencyRateDate datetime


The SWITCHOFFSET function adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC value.

SELECT SYSDATETIMEOFFSET()
2009-07-17 11:19:42.7380350 +08:00

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');
2009-07-16 22:19:42.7380350 -05:00

The TODATETIMEOFFSET function sets the time zone offset of an input date and time value. The main purpose of the TODATETIMEOFFSET function is to convert types that are not time zone aware to DATETIMEOFFSET by the given time zone offset. If the given date and time value is a DATETIMEOFFSET, the TODATETIMEOFFSET function changes the DATETIMEOFFSET value based on the same original local date and time value plus the new given time zone offset.

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-05:00');
2009-07-17 11:21:09.3356644 -05:00

This function is different from SWITCHOFFSET in several ways. First, it is not restricted to a DATETIMEOFFSET value as input; rather it accepts any date and time data type. Second, it does not try to adjust the time based on the time zone difference between the source value and the specified time zone but instead simply returns the input date and time value with the specified time zone as a DATETIMEOFFSET value.

SELECT TODATETIMEOFFSET(SYSDATETIME(), '-05:00');
2009-07-17 11:22:31.9018632 -05:00

DATEADD, DATEDIFF, DATENAME, and DATEPART functions are updated to add support for microseconds and nanoseconds. Many other functions are enhanced to support new date and time types, among them are, type conversion functions (CAST and CONVERT), set and aggregate functions (such as MAX, MIN), metadata functions (such as TYPEPROPERTY, COLUMNPROPERTY), and system functions (such as DATALENGTH, IS_DATE).

Hope this information is useful. Do let me know if you have any question.

Regards,

Happy SQL Coding