TSQL Date conversion

Question

I have a SQL table with datetime2 objects, and they are stored in UTC TIME

for example

UTC_TIME = 2017-10-20T13:16:14.6950000

I have successfully converted this timestamp to local time using

SELECT UTC_TIME AT TIME ZONE 'W. Europe Standard Time' AS LOCAL_TIME

The output of the above is

2017-10-20T13:16:14.6950000+02:00

This is correct, but the output I want is

2017-10-20T15:16:14.6950000

The offset should be added to my timestamp.

Is there an easy way to do this ?

Example : An event was logged on UTC time 2017-10-20T13:16:14.6950000 I'm in Western Europe, and for the moment there is an offset of 2 hours. So the actual local time is 2017-10-20T15:16:14.6950000

I would like to convert the UTC time (13:16), so that the result is 15:16

The query is executed on a server that runs in UTC timezone.

Possible duplicate of [How can I convert a Sql Server 2008 DateTimeOffset to a DateTime](https://stackoverflow.com/questions/4953903/how-can-i-convert-a-sql-server-2008-datetimeoffset-to-a-datetime) — Igor, Oct 20 '17 at 14:22

score 0 · Answer 1 · answered Oct 20 '17 at 13:39

0

As AT TIME ZONE returns a DateTimeOffset you can use CONVERT with option 1.

DECLARE @UTC_TIME DATETIME2(7) = '2017-10-20T13:16:14.6950000'

SELECT CONVERT(datetime2, (@UTC_TIME AT TIME ZONE 'Pacific Standard Time'), 1) AS OrderDate_TimeZonePST

See also CAST and CONVERT (Transact-SQL)

answered Oct 20 '17 at 13:39

Igor

60,821
10
100
175

This does not work :: DECLARE UTC_TIME DATETIME2(7) = '2017-10-20T13:16:14.6950000' SELECT UTC_TIME AS T0, UTC_TIME AT TIME ZONE 'W. Europe Standard Time' AS T1, CONVERT(datetime2, (UTC_TIME AT TIME ZONE 'W. Europe Standard Time'), 1) AS T2 answer I get back is T0 : 2017-10-20T13:16:14.6950000, T1 : 2017-10-20T13:16:14.6950000+02:00, T2 : 2017-10-20T11:16:14.6950000, but the expexted value is 2017-10-20T15:16:14.6950000 (as I would read on a local clock) – Kermit754 Oct 20 '17 at 14:04

score 0 · Answer 2 · answered Oct 20 '17 at 19:15

0

You can do this using sysdatetimeoffset

declare @UTC_Time datetime2 = '2017-10-20T13:16:14.6950000'

Select dateadd(minute, datepart(TZoffset, sysdatetimeoffset()), @UTC_Time)

answered Oct 20 '17 at 19:15

chrisuae

1,092
7
8

This does not work either, as the SQL server is running in the cloud, in UTC timezone ... – Kermit754 Oct 23 '17 at 07:58

score 0 · Answer 3 · answered Oct 23 '17 at 08:10

This seems the way to do it :

    DECLARE @UTC_TIME DATETIME2(7) = '2017-10-20T13:16:14.6950000'

    SELECT 
    @UTC_TIME AS T0, 
    @UTC_TIME AT TIME ZONE 'W. Europe Standard Time' AS T1,
    dateadd(minute,DATEdiff(minute,@UTC_TIME AT TIME ZONE 'W. Europe Standard Time',@UTC_TIME ),@UTC_TIME) as T2

The result is T0 : 2017-10-20T13:16:14.6950000

T1 : 2017-10-20T13:16:14.6950000+02:00

T2 : 2017-10-20T15:16:14.6950000

T2 is what we would see on the local clock for an event happening at UTC time T0

TSQL Date conversion

3 Answers3