Convert Datetime to other format

Question

Convert Datetime to other format

Bruin

SSCommitted

Points: 1829
More actions
June 13, 2022 at 6:32 pm

#4048918

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.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ratbak SSC Eights! Points: 939 More actions · Answer 1

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?

Bruin SSCommitted Points: 1829 More actions · Answer 2

Bruin

SSCommitted

Points: 1829

June 13, 2022 at 7:11 pm

#4048932

EST

ratbak SSC Eights! Points: 939 More actions · Answer 3

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.

Ed B Hall of Fame Points: 3130 More actions · Answer 4

Bruin wrote:

EST

Are you sure it's not EDT?

Now I see why AWS defaults to Zulu time.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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)
Intro to Tally Tables and Functions

Ed B Hall of Fame Points: 3130 More actions · Answer 6

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?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

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)
Intro to Tally Tables and Functions

Bruin SSCommitted Points: 1829 More actions · Answer 9

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..

Bruin SSCommitted Points: 1829 More actions · Answer 10

Bruin

SSCommitted

Points: 1829

June 14, 2022 at 1:11 am

#4049036

Current Offset: UTC/GMT -5 hours

Bruin SSCommitted Points: 1829 More actions · Answer 11

any help in how I can do the date format change?

Thom A SSC Guru Points: 99009 More actions · Answer 12

Bruin wrote:

any help in how I can do the date format change?

What was wrong with the wealth of replies above about using CONVERT with a style code (or combined with SUBSTRING etc)?

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 use SWITCHOFFSET. 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

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 13

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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Ed B Hall of Fame Points: 3130 More actions · Answer 14

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;