T-SQL Programmability Features in SQL server 2008 - Part 2

By Admin at 22 May 2010, 16:11 PM
  • New T-SQL Features in SQL server 2008 - Part 2


    Before reading this article we suggest to read
    New T-SQL Features in SQL server 2008 - Part 1

    4. New date and time data types:


    The previous versions of SQL Server had two date/time data types DATETIME and SMALLDATETIME. Both of them stored a DATE value and a TIME value together. SQL Server 2008 introduces four new date and time data types.

    • DATE
    • TIME
    • DATETIME2
    • DATETIMEOFFSET

    DATE Data Type


    A DATE data type stores a date value (year, month and day). It is close to what you get after you strip off the TIME portion from a DATETIME value. Date range is from January 1, 0001, through December 31, 9999.

    Example:

    1. DECLARE @dateDATE
    2. SET @date = '2010-01-01 01:11:11'
    3. SELECT @dateAS [Date]

    Result: 2010-01-01

    TIME Data Type


    The TIME data type stores a "time" value. The TIME data type is capable of supporting fractional precision up to 100 nanoseconds. TIME value is what is left when you remove the DATE part from a DATETIME value.

    Example:

    1. DECLARE @DateTime AS DATETIME= GETDATE()
    2. SELECT @DateTime

    Result:

    2010-04-30 16:48:43.170

    1. DECLARE @timeTIME = GETDATE()
    2. ELECT @time

    Result: 16:48:43.1700000

    DATETIME2 Data Type:


    DATETIME2 supports better precision (up to 100 nanoseconds) and supports a larger date range (between 1 January 0001 to 31 December 9999). You can specify the level of precision that you need, by passing an optional argument (0 to 7), default precision is 7.

    Example:

    1. SELECTCAST(sysdatetime() AS DATETIME2)

    Result: 2010-04-30 16:54:52.4062500

    1. SELECTCAST(sysdatetime() AS DATETIME2(0))

    Result:2010-04-30 16:54:52

    1. SELECTCAST(sysdatetime() AS DATETIME2(1))

    Result: 2010-04-30 16:54:52.4

    1. SELECTCAST(sysdatetime() AS DATETIME2(2))

    Result: 2010-04-30 16:54:52.41

    1. SELECTCAST(sysdatetime() AS DATETIME2(3))

    Result: 2010-04-30 16:54:52.406

    1. SELECTCAST(sysdatetime() AS DATETIME2(4))

    Result: 2010-04-30 16:54:52.4063

    1. SELECTCAST(sysdatetime() AS DATETIME2(5))

    Result: 2010-04-30 16:54:52.40625

    1. SELECTCAST(sysdatetime() AS DATETIME2(6))

    Result: 2010-04-30 16:54:52.406250

    1. SELECTCAST(sysdatetime() AS DATETIME2(7))

    Result: 2010-04-30 16:54:52.4062500

    DATETIMEOFFSET Data Type


    The DATETIMEOFFSET data type defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

    Format:
    YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]

    • YYYY is four digits, ranging from 0001 through 9999 that represent a year.
    • MM is two digits, ranging from 01 to 12, that represent a month in the specified year.
    • DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month.
    • hh is two digits, ranging from 00 to 23, that represent the hour.
    • mm is two digits, ranging from 00 to 59, that represent the minute.
    • ss is two digits, ranging from 00 to 59, that represent the second.
    • n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
    • hh is two digits that range from -14 to +14.
    • mm is two digits that range from 00 to 59.

    Example:

    1. SELECTCAST(sysdatetime() AS DATETIMEOFFSET)

    Result:

    2010-04-30 17:04:39.6875000 +00:00

    5. New date and time functions:


    To support the new date and time data types, SQL Server 2008 introduces new functions and enhances existing functions.

    The new functions are:

    SYSDATETIME, SYSUTCDATETIME

    • SYSDATETIME returns the current date and time as a DATETIME2 value
    • SYSUTCDATETIME returns the current date and time in UTC as a DATETIME2 value

    Example:

    1. SELECT SYSUTCDATETIME() AS DATE1,SYSDATETIME() AS DATE2

    Result:

    undefined

    • SYSDATETIMEOFFSET

    SYSDATETIMEOFFSET returns the current date and time along with the system time zone as a DATETIMEOFFSET value

    Example:

    1. SELECT SYSDATETIMEOFFSET()

    Result: 2010-04-30 17:55:14.3593750 +05:30

    • SWITCHOFFSET

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

    Example:

    1. SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+01:00') AS T,SYSDATETIMEOFFSET() AS D

    Result:

    undefined

    Example:

    1. DECLARE @TimeZoneDiff ASVARCHAR(6) = '-05:00'
    2. -- Convert from UTC date/time to current zone date/time
    3. SELECTCAST(SWITCHOFFSET(CAST(SYSUTCDATETIME() AS DATETIMEOFFSET), @TimeZoneDiff) as DATETIME2)
    4. -- Convert from current zone date/time to UTC date/time.
    5. SELECTCAST(SWITCHOFFSET(CAST(SYSDATETIME() AS DATETIMEOFFSET), @TimeZoneDiff) as DATETIME2)

    Result:

    undefined

    • TODATETIMEOFFSET

    The TODATETIMEOFFSET function sets the time zone offset of an input date and time value.

    Example:

    1. SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+01:00') AS T,SYSDATETIMEOFFSET() AS D

    Result:

    undefined

    Continue reading Merge statement and Grouping Sets features of T-SQL in


    T-SQL Programmability Features in SQL server 2008 - Part 3


    Comments

     

    Post a comment

    Please correct the following: