• Channels

  • Contact

  • Main Site

  • More

    Use tab to navigate through the menu items.
    To see this working, head to your live site.
    • Categories
    • All Posts
    • My Posts
    merv
    Jun 26, 2018
      ·  Edited: Jun 26, 2018

    What's the Time Please

    in Software Engineering

    Frequently we get requirements for UTC times in Azure SQL databases to be converted into various world time zones. Whilst scripting within the table builds and adding columns, or relying on sever settings, can produce this outcome there is a more flexible approach using the AT TIME ZONE (T-SQL) statement. Changes in Time Zone are automatically catered for throughout the year, dependent upon the date.


    Using that command within statements provides a simple set of results that can written into queries as below. In this instance it is UTC converted to cater for Daylight Saving in the UK


    This will produce results that provide the additional column, however additional columns add to storage and table sizes. We therefore took the approach of creating a SQL function that can be utilised in any query or view to create a more agile use of the command.


    Creating a function as the script below

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[TimeZoneUK] (@UTCDate AS DATETIMEOFFSET(3))

    RETURNS DATETIMEOFFSET(3)

    AS

    /*

    Description: Add UK Time Zone and Daylight Savings to UTC Datetime

    */

    BEGIN

    RETURN @UTCDate AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time'

    END

    GO


    Enabling us to utilise that function in SQL scripts and views without the need to repeat the AT TIME ZONE commands on each line by placing the function in from of the UTC DateTime column already in the database as below.


    SELECT

    CONVERT(INT,CONVERT(CHAR(10),(CAST(dbo.TimeZoneUK(TimestampCreated) as date)), 112) ) as [Date Key]

    ,DATEPART(HOUR,dbo.TimeZoneUK(TimestampCreated)) AS [Hour Key]

    ,CAST(dbo.TimeZoneUK(TimestampCreated) AS DATE) AS [Event Date]


    FROM [YourSchema].[YourTable]

    With the results converted to UK DST within the appropriate dates.




    Further details on the AT TIME ZONE command can be found within Microsoft's documentation here.


    https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017



    0 comments
    0 comments
    • Twitter Social Icon
    • LinkedIn Social Icon
    • Facebook Social Icon

    Visit the Elastacloud website