Convert Datetime to other format

Question

Post reply

Convert Datetime to other format

Viewing 15 posts - 16 through 30 (of 35 total)

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

Bruin SSCrazy Points: 2058 More actions · Answer 1

Bruin

SSCrazy

Points: 2058

June 15, 2022 at 6:56 pm

#4050054

This would be helpful... Thanks

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

FYI - your offsets are incorrect here. Eastern Time is currently set to -04:00 and Central European (summer time) is set to +02:00. The expected output you have is correct - but would not be correct if you are use -05:00 as your time zone offset.

I would also recommend not sending a string date in any of the known ambiguous formats. Instead - you should be sending the data using the ISO standard format YYYY-MM-DDTHH:MM:SS[+-]hh:mm. Using this format guarantees that the receiver interprets the date and time appropriately and does not try to convert DD/MM/YYYY to MM/DD/YYYY and fails.

If you sent the data as 2022-06-14T21:06:00.0000000-04:00 there would be no mistaking exactly what that date/time is - and they can easily convert it to the appropriate local time.

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

Bruin SSCrazy Points: 2058 More actions · Answer 3

Is there a table I could build to pass in my date and convert, or a function I could use...

My query s writing to a table that I need to convert 3 fields o the desired format I described abovee.

Example from my table:

2022-06-27 07:55:03.0000000

Bruin SSCrazy Points: 2058 More actions · Answer 4

Looks like I could do this:

switchoffset (CONVERT(datetimeoffset, jobstarted), '+04:00')

But how can I get just date\time and automate the offset based on fall\winter DST

Thanks.

Thom A SSC Guru Points: 99130 More actions · Answer 5

Bruin wrote:

switchoffset (CONVERT(datetimeoffset, jobstarted), '+04:00')
But how can I get just date\time and automate the offset based on fall\winter DST

Like myself and Jeffrey touched on earlier, you'll need to use a table to store the dates different places started and stopped observing DST for each year, as you are on an about to be unsupported version of SQL Server, that does not support AT TIME ZONE.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Bruin SSCrazy Points: 2058 More actions · Answer 6

How could I use the table from ED B. and perform the function I'm trying to accomplish?

Thanks.

Bruin SSCrazy Points: 2058 More actions · Answer 7

If I was SQL 2016 then...

JobStarted AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'Central European Standard Time' AS JobStarted_TimeZoneCET

That server will be moving to 2016 but need solution for 2012 until taht happens.

Thanks

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

Bruin wrote:

If I was SQL 2016 then...
JobStarted AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'Central European Standard Time' AS JobStarted_TimeZoneCET
That server will be moving to 2016 but need solution for 2012 until taht happens.
Thanks

Understood. Thom A. outlined the first necessary step to do that. Let us know when you've completed that step.

--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 SSC Guru Points: 1004356 More actions · Answer 9

BTw... the easier thing to do would be to convert your local dates and times to UTC and let the users fix their stuff. 😉

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

Bruin SSCrazy Points: 2058 More actions · Answer 10

okay so if I dump what ED B has for DST into a table .. what's next steps?

THx.

Ed B Hall of Fame Points: 3666 More actions · Answer 11

Bruin wrote:

okay so if I dump what ED B has for DST into a table .. what's next steps?
THx.

you can put the output into a table. Any east coast date time that falls within one of those spans is five hours behind Central European time. Any other date time is six hours behind. The default is six hours and there is a short period every spring and autumn where the difference is five. The tricky part is getting the east coast time at which the European summertime starts and ends so I would check the local times are correct for each year and that the calculated times are accurate.

Bruin SSCrazy Points: 2058 More actions · Answer 12

Could a function be created where I pass in my date and it outputs my desired date

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Bruin wrote:

Could a function be created where I pass in my date and it outputs my desired date

For 2012, yes, but that doesn't change step 1. You need a "table" of the DST rules either external to the function or internally.

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

Bruin SSCrazy Points: 2058 More actions · Answer 14

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)

)

Insert Into Dst_Time_Conv

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;

CREATE TABLE [dbo].[Dst_Time_Conv](

[CalendarYear] [varchar](4) NOT NULL,

[StartTime] [datetime2](7) NOT NULL,

[EndTime] [datetime2](7) NULL,

[TimeDiff] [int] NULL,

[TimePeriod] [varchar](25) NULL,

CONSTRAINT [PK_Dst_Time_Conv] PRIMARY KEY CLUSTERED

(

[CalendarYear] ASC,

[StartTime] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

Bruin SSCrazy Points: 2058 More actions · Answer 15

Bruin

SSCrazy

Points: 2058

June 28, 2022 at 6:27 pm

#4056217

sorry something wrong with formatting..