Convert Datetime to other format
-
I'm sending an export of a table and they would like to see the datetime fields converted to use:
Date format to be dd/mm/yyyy hh24:mi:ss, Rome Time
What the output looks like now is --> 2022-06-13 13:23:02.0000000
The SQL field def is --> jobstarted DateTime
Thanks.
-
Format could be derived like this:
CONVERT(CHAR(10),jobstarted,103) + ' ' + CONVERT(CHAR(8),jobstarted,108)
You indicate output as "Rome Time". What timezone/offset is your source data in?
-
EST
-
That conversion is going to be fun in SQL 2012. /s
In addition to the usual fun in converting among timezones in the United States, you have the additional complexity that Daylight Saving Time starts and ends on different dates for the two timezones in question.
This series regarding use of a calendar/DST table for timezone conversions might be of interest: https://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server-part-1/
Note that the time zone conversion will need to be done before/inside the format conversion.
-
- ratbak wrote:
Format could be derived like this:
CONVERT(CHAR(10),jobstarted,103) + ' ' + CONVERT(CHAR(8),jobstarted,108)
You indicate output as "Rome Time". What timezone/offset is your source data in?
I would recommend just forgetting that FORMAT even exists. FORMAT is at least 23 times slower than CONVERT even if you have to use multiple CONVERTs and substrings.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:ratbak wrote:
Format could be derived like this:
CONVERT(CHAR(10),jobstarted,103) + ' ' + CONVERT(CHAR(8),jobstarted,108)
You indicate output as "Rome Time". What timezone/offset is your source data in?
I would recommend just forgetting that FORMAT even exists. FORMAT is at least 23 times slower than CONVERT even if you have to use multiple CONVERTs and substrings.
Are you saying that CONVERT styles are the same as using FORMAT?
- Ed B wrote:Jeff Moden wrote:ratbak wrote:
Format could be derived like this:
CONVERT(CHAR(10),jobstarted,103) + ' ' + CONVERT(CHAR(8),jobstarted,108)
You indicate output as "Rome Time". What timezone/offset is your source data in?
I would recommend just forgetting that FORMAT even exists. FORMAT is at least 23 times slower than CONVERT even if you have to use multiple CONVERTs and substrings.
Are you saying that CONVERT styles are the same as using FORMAT?
A lot are but a lot aren't. I'm saying (and have proven with code many times in the past and recently) that "rolling your own" even with multiple CONVERTs or other functions (done properly, of course, and it's easy to do properly) produces code that's at least 23 times faster than FORMAT.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
For those interested, here's the profiler run I fan just a bit ago. It doesn't contain all the tests but it doesn't really matter because the extra tests just prove more of the same. And, yeah... convert even beat the BASELINE where there's no conversion because the output to the @Bitbucket variable was shorter.
Also, I limited each run to only running twice. The code that follows limits it to 3 times, just to be sure but same results. There is no case where FORMAT comes even close to even some of the more complicated CONVERT code.
And sorry about the coloring of the code. I normally try to fix it by doubling up the quote marks in the comments but I just don't have the time for this one and even the supposed "plain" text still makes a mess. I think it a bit horrible that a forum about SQL can't actually render SQL code properly even in the "plain" mode. 🙁
Here's the test code for all of that. You will need to grab a copy of my fnTally() function from the similarly named link below because that's what I used to quickly make the million row test table.
--- USE a safe database other than TempDB for this one
;
GO
--@@@@@ BUILD TEST TABLE @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
PRINT '--@@@@@ BUILD TEST TABLE @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@';
GO
--===== Conditionally Drop the Test Table =============================================================================
DROP TABLE IF EXISTS dbo.FormatTest;
GO
--===== Create the Test Table =========================================================================================
SET NOCOUNT OFF
;
CREATE TABLE dbo.FormatTest
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,SomeDT DATETIME
,Fluff CHAR(130) DEFAULT 'X'
)
;
GO
--===== Populate the Test Table =======================================================================================
DECLARE @LoDT DATETIME = '2020' --Inclusive Start Date
,@CoDT DATETIME = '2030' --Exlusive CutOff Date
;
INSERT INTO dbo.FormatTest WITH (TABLOCK) --Required for Minimal Logging
(SomeDT)
SELECT SomeDT = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,@LoDT,@CoDT)+@LoDT
FROM dbo.fnTally(1,1000000)
;
GO
--===== Let's see a sample of what the table contains... ==============================================================
SELECT TOP 1000 * FROM dbo.FormatTest
;
GO
--===== ... and how big it is. ========================================================================================
SELECT SizeMB = CONVERT(DECIMAL(9,3),page_count/128.0)
,IndexLevel = index_level
,PageCount = page_count
,Rows = record_count
,RowSize = avg_record_size_in_bytes
,LevelType = CASE
WHEN page_count = 1 THEN 'ROOT'
WHEN index_level = 0 THEN 'LEAF'
ELSE 'INTERMEDIATE'
END
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.FormatTest'),NULL,NULL,'DETAILED')
ORDER BY index_level DESC;
GO
--@@@@@ BASELINE @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
PRINT '--@@@@@ BASELINE @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@';
GO
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== BASELINE ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== BASELINE ======================================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = SomeDT
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--@@@@@ mm/dd/yyyy @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
PRINT '--@@@@@ mm/dd/yyyy @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@';
GO
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('===== FORMAT(MM/dd/yyyy) ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== FORMAT(MM/dd/yyyy) ============================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = FORMAT(SomeDT,'MM/dd/yyyy')
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== CONVERT 101 (mm/dd/yyyy) ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== CONVERT 101 (mm/dd/yyyy) ======================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONVERT(VARCHAR(50),SomeDT,101)
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--@@@@@ (m)m/(d)d/yyyy) @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
PRINT '--@@@@@ (m)m/(d)d/yyyy) @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@';
GO
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('===== FORMAT(d) (m)m/(d)d/yyyy) ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== FORMAT(d) ====================================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = FORMAT(SomeDT,'d') --This DOES have the advantage of automatically adapting to the current language.
FROM dbo.FormatTest --If you're having performance issues, then may not be worth it.
; --The end use may also not tolerate the change to dd/mm/yyyy format.
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== CONCAT w/DATENAME (m)m/(d)d/yyyy) ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== CONCAT w/DATENAME (m)m/(d)d/yyyy) ================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONCAT(DATEPART(mm,SomeDT),'/',DATEPART(dd,SomeDT),'/',DATEPART(yy,SomeDT))
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== CONCAT_WS w/DATENAME ((m)m/(d)d/yyyy) ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== CONCAT_WS w/DATENAME (m)m/(d)d/yyyy) =============================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONCAT_WS('/',DATEPART(mm,SomeDT),DATEPART(dd,SomeDT),DATEPART(yy,SomeDT))
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--@@@@@ DDDD, MMMM (D)D, yyyy @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
PRINT '--@@@@@ DDDD, MMMM (D)D, yyyy @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@';
GO
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== FORMAT(D) ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== FORMAT(D) =====================================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = FORMAT(SomeDT,'D') --This also adapts to language but will make the formatting a bit unpredictable.
FROM dbo.FormatTest --For example, it removes the commas when the language is French.
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== CONCAT_WS w/CONVERT/DATEPART (Original) ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== CONCAT_WS w/CONVERT (Original)) ===============================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONCAT_WS(', '
,DATENAME(WEEKDAY,SomeDT)
,DATENAME(MONTH,SomeDT) + ' '
+CONVERT(VARCHAR(2), DATEPART(DAY,SomeDT))
,CONVERT(CHAR(4), DATEPART(YEAR,SomeDT)))
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== CONCAT_WS w/DATENAME (Simplified) ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== CONCAT_WS w/DATENAME (Simplified) =============================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONCAT_WS(', '
,DATENAME(dw,SomeDT)
,DATENAME(mm,SomeDT) + ' ' + DATENAME(dd,SomeDT)
+DATENAME(yy,SomeDT))
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== CONCAT w/DATENAME ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== CONCAT w/DATENAME =============================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONCAT(
DATENAME(dw,SomeDT),', '
,DATENAME(mm,SomeDT),' ',DATENAME(dd,SomeDT),', '
,DATENAME(yy,SomeDT))
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== BRUTE FORCE ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== BRUTE FORCE ===================================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = DATENAME(dw,SomeDT)+', '
+ DATENAME(mm,SomeDT)+ ' '+DATENAME(dd,SomeDT)+', '
+ DATENAME(yy,SomeDT)
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== ELEGANT w/CHOOSE ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== ELEGANT w/CHOOSE ==============================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONCAT(
DATENAME(dw,SomeDT),', '
,DATENAME(mm,SomeDT),' '
,DATENAME(dd,SomeDT)
,CHOOSE(DATEPART(dd,SomeDT)%10+1,'th','st','nd','rd','th','th','th','th','th','th'),', '
,DATENAME(yy,SomeDT))
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== ELEGANT w/CASE ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== ELEGANT w/CASE ================================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONCAT(
DATENAME(dw,SomeDT),', '
,DATENAME(mm,SomeDT),' '
,DATENAME(dd,SomeDT)
,CASE DATEPART(dd,SomeDT)%10
WHEN 1 THEN 'st'
WHEN 2 THEN 'nd'
WHEN 3 THEN 'rd'
ELSE 'th'
END
,', '
,DATENAME(yy,SomeDT))
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--=====================================================================================================================
PRINT REPLICATE('=',119);
RAISERROR('========== ELEGANT w/SUBSTRING ==========',0,0) WITH NOWAIT;
--=====================================================================================================================
SET STATISTICS TIME,IO OFF;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== ELEGANT w/SUBSTRING ===========================================================================================
SET STATISTICS TIME ON;
DECLARE @BitBucket VARCHAR(50)
;
SELECT @BitBucket = CONCAT(
DATENAME(dw,SomeDT),', '
,DATENAME(mm,SomeDT),' '
,DATENAME(dd,SomeDT)
,SUBSTRING('thstndrdthththththth',DATEPART(dd,SomeDT)%10*2+1,2),', '
,DATENAME(yy,SomeDT))
FROM dbo.FormatTest
;
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('-',119);
GO 3
--@@@@@ Run Complete @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
PRINT '--@@@@@ Run Complete @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@';
GO--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
i'm running my process local EST and want it converted to:
Current:
CEST — Central European Summer Time
UTC/GMT +2 hours
ON my server it's 21:06 I want that displayed as Tuesday, June 14, 2022 03:06am
Thanks..
-
Current Offset: UTC/GMT -5 hours
-
any help in how I can do the date format change?
- Bruin wrote:
any help in how I can do the date format change?
What was wrong with the wealth of replies above about using
CONVERTwith a style code (or combined withSUBSTRINGetc)?Changing the timezone is harder; you're on SQL Server 2012 so don't have access to
AT TIME ZONE(I really recommend getting that upgrade path sorted, 2012 has about 4 weeks of extended support left). Do you always need to go to UTC+2, or will it need to be UTC+1 during the winter? If it's just always going to UTC+2, you can useSWITCHOFFSET. If you need to observe DST, you'll going to need to have somewhere to store the dates for when both DST is in the destination timezone; so that you can switch appropriately (that is going to be a pain).Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I would recommend working with the receivers of the data to allow sending UTC directly instead of trying to perform the conversion for them. If you send UTC they can then convert it to their local time as needed much easier than you trying to figure out their local time.
An alternative would be to send it using a datetimeoffset data type - which would include the timezone offset value. They could then utilize that value to convert to either UTC or local time on their systems as needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
This might work for creating a table of local date times when Eastern Daylight Savings and Central European Summertime start and end for the next 10 years (based on current rules). The gaps between the two start dates and the two end dates correspond to a 5 hour time difference, at all other times there is a 6 hour difference.
- CEST Start: Last Sunday of March at 2 am
- CEST End: Last Sunday of October at 3 am
- EDT Start: Second Sunday of March at 2am
- EDT End: First Sunday of November at 2 am
I think that by subtracting the correct number of hours, the central European times can be converted to local East coast times allowing time spans when the difference is 5 hours. Outside of these spans the difference is 6 hours. I think this works in principle, but I may well have got the dateadd -5 and -6 mixed up. With the hard coded 5 and 6 it only works for Eastern US and Central Europe. If this is at all useful, I would double check the dates.
WITH Daylight AS
(
SELECT *
FROM
(VALUES
('2022', '2022-03-27 02:00:00', '2022-10-30 03:00:00', '2022-03-13 02:00:00', '2022-11-06 02:00:00'),
('2023', '2023-03-26 02:00:00', '2023-10-29 03:00:00', '2023-03-12 02:00:00', '2023-11-05 02:00:00'),
('2024', '2024-03-31 02:00:00', '2024-10-27 03:00:00', '2024-03-10 02:00:00', '2024-11-03 02:00:00'),
('2025', '2025-03-30 02:00:00', '2025-10-26 03:00:00', '2025-03-09 02:00:00', '2025-11-02 02:00:00'),
('2026', '2026-03-29 02:00:00', '2026-10-25 03:00:00', '2026-03-08 02:00:00', '2026-11-01 02:00:00'),
('2027', '2027-03-28 02:00:00', '2027-10-31 03:00:00', '2027-03-14 02:00:00', '2027-11-07 02:00:00'),
('2028', '2028-03-26 02:00:00', '2028-10-29 03:00:00', '2028-03-12 02:00:00', '2028-11-05 02:00:00'),
('2029', '2029-03-25 02:00:00', '2029-10-28 03:00:00', '2029-03-11 02:00:00', '2029-11-04 02:00:00'),
('2030', '2030-03-31 02:00:00', '2030-10-27 03:00:00', '2030-03-10 02:00:00', '2030-11-03 02:00:00')
) AS x (CalendarYear, CEDTStart, CEDTEnd, EDTStart, EDTEnd)
)
SELECT CalendarYear, DATEADD(HOUR,-6,CEDTEnd) AS StartTime, CEDTEnd AS EndTime, 5 AS TimeDiff, 'Fall Back' AS 'TimePeriod'
FROM Daylight
UNION ALL
SELECT CalendarYear, EDTStart, DATEADD(HOUR,-5,CEDTStart) , 5 AS TimeDiff, 'Spring Forward' AS 'TimePeriod'
FROM Daylight
ORDER BY Calendaryear, StartTime;
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply