Friday, July 17, 2009

Date and Time data type in SQL Server 2008


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



  • 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


  • 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:
    @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:
    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.
    2009-07-17 11:19:42.7380350 +08: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.
    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.
    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.


    Happy SQL Coding

    No comments: