Changing the time zone for a Synapse dedicated SQL pool

pmscorca 812 Reputation points
2024-04-24T12:41:21.9433333+00:00

Hi,

inside a Synapse dedicated SQL pool I need to use the GETDATE() T-SQL function for some queries, but it returns a time minor of two hours respect to the Italian time.

This is the datetime returned by the GETDATE() using SSMS:

User's image

The real time is 14:35.

Also a my colleague has the same issue for the same Azure subscription and Synapse Analytics.

The SQL pool is into a West Europe resource group.

So, is it possible to change the Synapse time zone or to adjust the datetime returned by the GETDATE function?

Thanks

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,461 questions
{count} votes

1 answer

Sort by: Most helpful
  1. phemanth 6,630 Reputation points Microsoft Vendor
    2024-04-24T14:02:22.28+00:00

    @pmscorca

    Thanks for reaching out to Microsoft Q&A

    The time zone of a dedicated SQL pool in Azure Synapse Analytics cannot be changed. The dedicated SQL pool uses the time zone of the Azure region it's deployed in, which in your case is likely UTC. This means that the GETDATE() function will always return the time in UTC.

    Here are two ways to adjust the datetime returned by GETDATE() to Italian time:

    • Use the AT TIME ZONE clause: You can add the AT TIME ZONE clause to the GETDATE() function to specify the desired time zone. For example, the following query would return the current time in Italian time:
    SELECT GETDATE() AT TIME ZONE 'Central European Time' AS ItalianTime;
    
    • Convert the UTC time to Italian time after it is returned by GETDATE(): You can use the DATEADD function to convert the UTC time to Italian time. For example, the following query would add two hours to the current UTC time to get the current Italian time:
    SELECT DATEADD(HOUR, 2, GETDATE()) AS ItalianTime;
    
    

    The query in the image you sent uses both methods to achieve the same result. The first three columns all return the same value, which is the current time in UTC. The fourth column uses the AT TIME ZONE clause to convert the UTC time to Central European Time.

    Hope this helps. Do let us know if you any further queries.


    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.