I felt like this should be easy but I'm not finding answer. The query below will give me the current date/time in California and represent it as UTC with a timezone offset (currently -08:00):
select getdate() at time zone 'Pacific Standard Time'
I simply want to apply the offset and get a timestamp showing the current local time and then drop the offset. I feel like there should be a way to achieve this without having to pick through it with string and date functions. For example, I want to go from
2021-11-24 18:03:41.190 -08:00
to
2021-11-24 10:03:41.190
Is there a succinct way to do this?
CodePudding user response：
When you use AT TIME ZONE on a value that isn't a datetimeoffset it's assumed that the value is at the time zone you are converting it to.
Instead, therefore, you could nest the AT TIME ZONE clauses. If you did do this though, I would also suggest using SYSUTCDATE rather than GETDATE (which returns the local time to the host, which could be any timezone).
SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time';
Though really, just use SYSDATETIMEOFFSET to start with; then you just need one AT TIME ZONE as the RDBMS already knows what timezone the value is for.
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time';
CodePudding user response：
Declare @myDateTime datetime = '2021-11-24 21:35:25.984'; --UTC time
Select cast(@myDateTime At Time Zone 'UTC' At Time Zone 'Pacific Standard Time' As datetime);
Or - you can just apply the desired offset and convert back to datetime
Select cast(switchoffset(@myDateTime, '-08:00') As datetime)
The problem with using switchoffset is that it isn't DST aware and would need to be changed to use a different offset value. If you change the date above to October - the PST time would be 14:35:25.983 but the second calculation still returns 13:35:25.983