Convert Date Time to EST

1

Hello.

Here are the data fields I have. Date and time that is currently Pacific Standard Time:

EntryDate = 06/28/2023
TransactionTime = 21:08

How can I convert and concatenate this information so that it reflects Eastern Standard Time ... always, even for time zones that don't have DLS so it looks like this?

What I want is = 06/29 21:08

Thank you.

2

What are the data types of the data 'fields' you have?

You can concatenate the date and time and cast that as a datetime or datetime2

CAST(CONCAT_WS(' ', EntryDate, TransactionTime)) AS datetime2)

Once you have a valid datetime or datetime2 data type - you can then use AT TIME ZONE:

SELECT CAST(CONCAT_WS(' ', EntryDate, TransactionTime)) AS datetime2) AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'Eastern Standard Time'

This returns '2023-06-29 00:08:00.0000000 -04:00' - which is 3 hours difference between PST and EST.

The first AT TIME ZONE identifies the given date/time as PST - the second then converts that date/time to EST.

FWIW - I wouldn't choose to convert the date/time to EST always. I would be converting everything to UTC always. That way it is very easy to convert to any other time zone as needed:

DECLARE @datetime_utc datetimeoffset(7) = '2023-06-29 04:08:00.0000000';  -- UTC
 SELECT @datetime_utc AT TIME ZONE 'Pacific Standard Time'
      , @datetime_utc AT TIME ZONE 'Central Standard Time'
      , @datetime_utc AT TIME ZONE 'Eastern Standard Time'