UTC time to EST time

Question

UTC time to EST time

adisql

SSCrazy

Points: 2893
More actions
June 8, 2020 at 2:58 am

#3761216

Hi,
The 'TIME_UTC' column is UTC timing. I would like to generate the report for EST timings .
But below query gives UTC timings for the last week.I would like to convert it to EST timing. Any suggestions pls.
select TIME_UTC from table_name
where TIME_UTC BETWEEN cast(GETDATE()-8 as date) AND cast(GETDATE()-1 as date)

Thanks.

June 10, 2020 at 2:39 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

I suspect you want SWITCHOFFSET. For example, at the time of writing, SELECT SWITCHOFFSET(GETUTCDATE(),'-05:00'); returns 2020-06-08 04:23:38.100 -05:00.

If you are using this in a WHERE you are better off changing the OFFSET of your parameter, not the column. In Pseudo-SQL:

WHERE YourUTCDateTime >= SWITCHOFFSET(@YourESTDatetimeOffsetStart,'+00:00')
  AND YourUTCDateTime < SWITCHOFFSET(@YourESTDatetimeOffsetEnd,'+00:00')

This reply was modified 4 years, 6 months ago by Thom A.

Thom~

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

What's the datatype of the column? Is it a straight datetime/datetime2? Or something like datetimeoffset?

Steve Collins SSCrazy Points: 2172 More actions · Answer 3

In 2016 there are some nice built in features to support time zone conversions

/* the list of time zones your instance supports */
select * from sys.time_zone_info;

declare
  @utc_dt           datetime=sysutcdatetime();

/* the UTC date */
select @utc_dt;

/* whatever time zone from sys.time_zone_info */
select @utc_dt at time zone 'Alaskan Standard Time';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

adisql SSCrazy Points: 2893 More actions · Answer 4

adisql

SSCrazy

Points: 2893

June 9, 2020 at 7:04 pm

#3761784

datatype is datetime

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

You don't have time zone info, so you can convert this based on EST, knowing that some of your data will be incorrect. During parts of the year, EST is used, parts you have EDT. If this doesn't matter, when pick one (4 or 5 hours) and adjust times with DATEADD or SWITCHOFFSET()

Just note that SWITCHOFFSET returns a datetimeoffset, which should convert implicitly to datetime.