Convert Datetime to other format

Question

Post reply

Convert Datetime to other format

Ed B

Hall of Fame

Points: 3623
More actions
June 28, 2022 at 6:41 pm
Answer
#4056242
duplicate removed
- This reply was modified 1 year, 10 months ago by Ed B.

Viewing 6 posts - 31 through 35 (of 35 total)

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

Ed B Hall of Fame Points: 3623 More actions · Answer 1

Bruin wrote:

sorry something wrong with formatting..

The code formatting window has gone, but something like this.

DECLARE @JobTime DATETIME2 = '2022-03-14 12:23:45.0000000'

SELECT CASE WHEN EXISTS (SELECT 1 FROM [dbo].[Dst_Time_Conv] WHERE @JobTime >= StartTime AND @JobTime < EndTime)

THEN DATEADD(HOUR,5,@JobTime)

ELSE DATEADD(HOUR,6,@JobTime)

END AS CEJobTime

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

Jeffrey Williams wrote:

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.

I didn't see this post before. I totally agree, espedcially since the DATETIEMOFFSET datatype is available as of 2008. TZOFFSET is also a choice in DATEPART and there are other TX functions available as well.

--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: 1003894 More actions · Answer 3

No guarantees but I'll try to take a look tonight. I'll use your original post as an example. I still maintain that if you ship them with UTC's, they should be able to do the conversion on their end so that you don't have to ensure that you're sending the correct time with all that DST stuff, etc. There's also a "rumor" that the U.S is going to stay on DST instead of "falling back" in the fall of 2023.

--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: 1003894 More actions · Answer 4

I looked back through this thread and found the following.

Bruin wrote:

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.

I also looked at Ed B.'s table. It seems to have enough information and Ed B. provided a suggestion for usage.

You know that that the EST time zone is a -4:00 when not on DST. You Also know that Rome has a certain offset during non-DST times. That also means you know the correct offset for non-DST time between EST and CEST. The next thing to do is find the row in Ed B's table for EST time... If it falls between the dates, that means it was daylight savings time and you're going to need to add 1 to the difference you previously calculated. Then you need to look for the same thing for the CEST time. If they're on DST, then subtract 1.

Disclaimer... the process above is accurate, I haven't tested the +/-1 thing. You should.

--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: 2018 More actions · Answer 5

Thanks for ALL responses with ED B help and suggestion from Jeff I think I have enough info to procceed.

Thanks again.