none
AT TIME ZONE in SQL Query RRS feed

  • Question

  • Hi All,

    Can you please suggest how to convert date time for below query with ‘AT TIME ZONE’? Required time zone is : Eastern European Summer Time (EEST)

    SELECT CREATEDON, NAME, ADDRESS
    FROM BASETABLE
    WHERE CreatedOn between '10-04-2018 08:43:29' and '11-04-2018 08:43:29'

    Thanks in advance.


    Thursday, August 22, 2019 6:02 AM

All replies

  • See AT TIME ZONE (Transact-SQL) for the correct syntax & examples.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, August 22, 2019 6:41 AM
  • Thanks Olaf.

    I have SP, it's written with complex logic with lot of datetime columns. Is there any other option/solution to set required time zone at the beginning of SP....so entire SP can use same time zone..  

    Thursday, August 22, 2019 7:24 AM
  • Hi PraveenKumar,

    Hope the below one will helps you!
    1. Create a user defined function for handling Time Zones like below.
    
    CREATE OR ALTER FUNCTION dbo.udf_getdate()
    RETURNS DATETIME
    AS 
    BEGIN
    	DECLARE @Current_DateTime DATETIME;
    	SELECT @Current_DateTime=SYSDATETIMEOFFSET() AT TIME ZONE 'Middle East Standard Time';  
    	RETURN @Current_DateTime ;
    END;
    
    2. Then, use the below local variable declare method in every stored procedures .
    
    DECLARE @Current_Date DATETIME;
    SELECT @Current_Date=dbo.udf_getdate();
    SELECT @Current_Date;
    
    In this way, we can simply change/handle the Time Zones in the function itself instead of handling everywhere in the codes.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi
    • Proposed as answer by Shah EBanned Thursday, August 22, 2019 7:07 PM
    Thursday, August 22, 2019 6:35 PM
  • Hi Arulmouzhi,

    I have created UDF as per your suggestion, Can you please check/review the below select query (is there any changes required to call UDF in below sample sql query)? Thanks in advance for your help and advise..

    DECLARE @Current_Date DATETIME;
    SELECT @Current_Date=dbo.udf_getdate();
    --SELECT @Current_Date As [Middle East Standard Time];
    select top 1
        CONVERT(varchar(100),CID ) AS [ID],
    	CONVERT(DATETIME, CreatedOn) AS [Open_Date],
    	CONVERT(varchar(100),CreatedByName) AS [Close_User]
    FROM BASETABLE
    WHERE CreatedOn between '10-04-2018 08:43:29' and '11-04-2018 08:43:29'

    Friday, August 23, 2019 3:36 AM
  • https://docs.microsoft.com/zh-cn/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017
    Friday, August 23, 2019 8:34 AM
  • Hi PraveenKumar,

    Attached the below Sample query for your ref. you can use like that way in your query. 

    DECLARE @Current_Date DATETIME,@end_dte DATETIME; --DECLARING VARIABLE FOR FETCHING CURRENT DATETIME
    SELECT @Current_Date=dbo.udf_getdate(); --USING OUR UDF HERE. (DATE WITH TIME)
    SET @Current_Date=CAST(@Current_Date AS DATE); --indicates current date from 12 AM
    SELECT @end_dte = @Current_Date + ' 23:59:59.997 '; --indicates end date to 11.59 PM
    
    SELECT TOP 10 [Order_Key] FROM dbo.[order] WHERE OrderDate BETWEEN @Current_Date AND @end_dte
    ORDER BY Order_Key DESC; --USING THAT IN QUERIES

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    Friday, August 23, 2019 10:20 AM