locked
AT TIME ZONE RRS feed

  • Question

  • User1203305613 posted

     [dbo].[UD_HabilitationServiceNote].[StartTime]    at time zone 'US Eastern Standard Time',
     [dbo].[UD_HabilitationServiceNote].[StartTime]

    i HAVE MY SELECT AS 

    SELECT 

     [dbo].[UD_HabilitationServiceNote].[StartTime]    at time zone 'US Eastern Standard Time',
     [dbo].[UD_HabilitationServiceNote].[StartTime]

    FROM  [dbo].[UD_HabilitationServiceNote]

    It returns the below result. 


    2021-03-09 16:00:01.000 -05:00    2021-03-09 16:00:01.000
    2021-03-11 16:00:26.000 -05:00    2021-03-11 16:00:26.000
    2021-03-16 15:00:53.687 -04:00    2021-03-16 15:00:53.687
    2021-03-18 15:00:03.000 -04:00    2021-03-18 15:00:03.000
    2021-03-23 15:00:14.607 -04:00    2021-03-23 15:00:14.607
    2021-03-30 15:00:02.000 -04:00    2021-03-30 15:00:02.000

    I want 2021-03-09 16:00:01.000 -05:00  to be converted as 2021-03-09 11:00:01.000  

    this 2021-03-30 15:00:02.000 -04:00   as 2021-03-30 11:00:02.000 

    How to accomplish this.

    Friday, April 9, 2021 1:04 PM

Answers

  • User475983607 posted

    Makes no logical sense but here you go...

    IF OBJECT_ID('tempdb..#DateDemo') IS NOT NULL
    	DROP TABLE #DateDemo;
    
    CREATE TABLE #DateDemo 
    (
    	StartTime DATETIME
    );
    
    INSERT INTO #DateDemo (StartTime) 
    VALUES ('2021-03-30 15:00:02.000');
    
    SELECT	StartTime at time zone 'US Eastern Standard Time',
    		DATEADD(
    			MINUTE, 
    			DATEPART(TZoffset, CAST( StartTime at time zone 'US Eastern Standard Time' as DATETIMEOFFSET)), 
    			StartTime) AS [StartTime+timeZoneOffset]
    FROM #DateDemo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 9, 2021 3:37 PM
  • User475983607 posted

    Thanks much, if you notice the two times will result to 11:AM as one was before Daylight Savings and other was after Daylight Savings. 

    '2021-03-30 15:00:02.000'

    '2021-03-09 16:00:01.000'

    When this is applied  Hour would be 11 AM

    Come on man!  You can try this yourself.

    IF OBJECT_ID('tempdb..#DateDemo') IS NOT NULL
    	DROP TABLE #DateDemo;
    
    CREATE TABLE #DateDemo 
    (
    	StartTime DATETIME
    );
    
    INSERT INTO #DateDemo (StartTime) 
    VALUES	('2021-03-30 15:00:02.000'),
    		('2021-03-09 16:00:01.000');
    
    SELECT	StartTime at time zone 'US Eastern Standard Time' as StartTime,
    		DATEADD(
    			MINUTE, 
    			DATEPART(TZoffset, CAST( StartTime at time zone 'US Eastern Standard Time' as DATETIMEOFFSET)), 
    			StartTime) AS [StartTime+timeZoneOffset]
    FROM #DateDemo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 9, 2021 6:18 PM

All replies

  • User475983607 posted

    KALYANA ALLAM

    I want 2021-03-09 16:00:01.000 -05:00  to be converted as 2021-03-09 11:00:01.000  

    The difference is the first date is formatted for EST (UTC-5) while the second date is EST-5.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/getutcdate-transact-sql?view=sql-server-ver15

    Friday, April 9, 2021 1:10 PM
  • User1203305613 posted

    Thanks mgebhard is there is a function to convert from '2021-03-09 16:00:01.000 -05:00' to  '2021-03-09 11:00:01.000 '

    I am not looking for current system time.

    Friday, April 9, 2021 1:46 PM
  • User475983607 posted

    KALYANA ALLAM

    Thanks mgebhard is there is a function to convert from '2021-03-09 16:00:01.000 -05:00' to  '2021-03-09 11:00:01.000 '

    I am not looking for current system time.

    I don't understand the logic.  Do you want to store dates as the current UTC time?

    DECLARE @date DATETIME2 = SYSDATETIME(); 
    DECLARE @utc DATETIME2 = SYSUTCDATETIME()
    SELECT @date, @utc

    Friday, April 9, 2021 2:52 PM
  • User1203305613 posted

    I have table  NOTE  which has column called StartTime

    When I run the below query 

    SELECT 

     [StartTime]    at time zone 'US Eastern Standard Time',
     [StartTime]
    FROM  NOTE

    It gives me result as 
    2021-03-30 15:00:02.000 -04:00    2021-03-30 15:00:02.000

    I want to add another column which subracts -4.00 from 2021-03-30 15:00:02.000 and give me result
    as 2021-03-30 11:00:02.000

    Friday, April 9, 2021 3:12 PM
  • User475983607 posted

    Makes no logical sense but here you go...

    IF OBJECT_ID('tempdb..#DateDemo') IS NOT NULL
    	DROP TABLE #DateDemo;
    
    CREATE TABLE #DateDemo 
    (
    	StartTime DATETIME
    );
    
    INSERT INTO #DateDemo (StartTime) 
    VALUES ('2021-03-30 15:00:02.000');
    
    SELECT	StartTime at time zone 'US Eastern Standard Time',
    		DATEADD(
    			MINUTE, 
    			DATEPART(TZoffset, CAST( StartTime at time zone 'US Eastern Standard Time' as DATETIMEOFFSET)), 
    			StartTime) AS [StartTime+timeZoneOffset]
    FROM #DateDemo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 9, 2021 3:37 PM
  • User1203305613 posted

    Thanks much, if you notice the two times will result to 11:AM as one was before Daylight Savings and other was after Daylight Savings. 

    '2021-03-30 15:00:02.000'

    '2021-03-09 16:00:01.000'

    When this is applied  Hour would be 11 AM

    DATEADD(
    			MINUTE, 
    			DATEPART(TZoffset, CAST( StartTime at time zone 'US Eastern Standard Time' as DATETIMEOFFSET)), 
    			StartTime)

    
    INSERT INTO #DateDemo (StartTime) 
    VALUES ('2021-03-30 15:00:02.000');
    
    INSERT INTO #DateDemo (StartTime) 
    VALUES
    
    
             ('2021-03-09 16:00:01.000');   
    Friday, April 9, 2021 5:54 PM
  • User475983607 posted

    Thanks much, if you notice the two times will result to 11:AM as one was before Daylight Savings and other was after Daylight Savings. 

    '2021-03-30 15:00:02.000'

    '2021-03-09 16:00:01.000'

    When this is applied  Hour would be 11 AM

    Come on man!  You can try this yourself.

    IF OBJECT_ID('tempdb..#DateDemo') IS NOT NULL
    	DROP TABLE #DateDemo;
    
    CREATE TABLE #DateDemo 
    (
    	StartTime DATETIME
    );
    
    INSERT INTO #DateDemo (StartTime) 
    VALUES	('2021-03-30 15:00:02.000'),
    		('2021-03-09 16:00:01.000');
    
    SELECT	StartTime at time zone 'US Eastern Standard Time' as StartTime,
    		DATEADD(
    			MINUTE, 
    			DATEPART(TZoffset, CAST( StartTime at time zone 'US Eastern Standard Time' as DATETIMEOFFSET)), 
    			StartTime) AS [StartTime+timeZoneOffset]
    FROM #DateDemo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 9, 2021 6:18 PM
  • User1203305613 posted

    MGEBHARD,I Did thank you soo much you can close this thread.

    Saturday, April 10, 2021 7:05 AM