1

I'm trying to display datetime values in local time. By default, Azure SQL Database stores dates and times in UTC, there is no way around this. (This was a pain when migrating from on premise SQL Server.) I would like to display a stored time value in Central European time.

Now the local time is 11:30 (CET). UTC time is 10:30.

DECLARE @TestTime DATETIME;
SET @TestTime = '2016-11-02 10:30:00'

SELECT @TestTime
--Returns 2016-11-02 10:30:00
SELECT @TestTime AT TIME ZONE 'Central European Standard Time'
--Returns 02 November 2016 10:30:00 +01:00

I need to return 2016-11-02 11:30:00 somehow. Now for the fun part:

As has been suggested here:

SELECT convert(DATETIME,@TestTime AT TIME ZONE 'Central European Standard Time',1)
--Returns 2016-11-02 09:30:00 So instead of adding the timezonedifference it subtracts it.

This works, but makes me sick:

SELECT DATEADD(MINUTE,DATEPART(tz,@TestTime AT TIME ZONE 'Central European Standard Time'),@TestTime)
--Returns 2016-11-02 11:30:00

A similar solution has been suggested here, but it plays with string operations. My suspicion is that something is wrong in AT TIME ZONE; it should have displayed 11:30 +1, and not 10:30 +1, no?

Is there really no proper way to display a UTC time in local time? This "hacking around it" feels awfully dirty, especially since at any point in time it just might stop working (e.g. Microsoft fixes / introduces a bug).

Thanks!

Community
  • 1
  • 1
vacip
  • 5,246
  • 2
  • 26
  • 54
  • What are you trying to display the local date time in, in the end? – Dan Rediske Nov 02 '16 at 16:49
  • @drediske In [CET](https://www.timeanddate.com/time/zones/cet). All I want to see in the example is 11:30. (Actually my db has users from a few other timezones, and daylight saving comes to play too, so it is a bit more complex.) It boils down to being able to convert the stored UTC time values to the desired local time. – vacip Nov 02 '16 at 17:32
  • I love these uncommented -1s... I'd love to improve the question if the voter enlightened me. – vacip Nov 02 '16 at 17:33
  • 1
    I know you wanted CET- I meant, how are you exposing the data? Your conversion may be better suited (say on a webpage) by using the local system timezone than the CET hardset. UTC (and the cloud) is de-localized, which you want to undo. Why not do it at the last possible moment (say, in your webpage)? – Dan Rediske Nov 02 '16 at 17:36
  • @drediske Oh, I see now. These timestamps are order times. The do not depend on the user's local settings, but on the country of residence of the company from which they order. This setting comes from the DB - every company has a TimeZone field. You are right on the "as late as possible" thing. I'm only putting together Views and SP-s to be used on the website, I'm not trying to store the localized time.. And seeing that our web devs are bleeding through all pores, I thought I'd take some load off them and serve up ready to use data. – vacip Nov 02 '16 at 17:51
  • tl, dr: I'm creating views and SPs that serve the website the required order data, where times must be displayed in a specified time zone. – vacip Nov 02 '16 at 17:55
  • So you're looking for use case A, roughly? https://msdn.microsoft.com/en-us/library/mt612795.aspx – Dan Rediske Nov 02 '16 at 18:11
  • @drediske Yes, but that just doesn't work that way as explained in their example. I'm puzzled here. I must be missing something very obvious. By the way, thanks for the help! – vacip Nov 02 '16 at 18:30

1 Answers1

5

The AT TIME ZONE statement performs two distinct operations:

  1. To assert that a datetime (or datetime2) is in a particular time zone, thus looking up the correct offset for that zone and returning a datetimeoffset value with the correct offset applied.

  2. To convert a datetimeoffset value to a different time zone, using the offset from the source value to pin down an exact point in time, then looking up the new offset for the requested time zone and applying it. This returns a datetimeoffset with a potentially different local time and offset than the original, but representing the same moment in time.

You are using the first part only. To convert a datetime from UTC to a specific time zone, you'll need to use both.

SELECT @TestTime AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time'

The first AT TIME ZONE asserts that the input datetime is in UTC, resulting in a datetimeoffset that has +00:00 for the offset. The second AT TIME ZONE converts that to the time zone requested.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • 1
    Also, I notice your profile says you're in Budapest, so it would be more correct to use the `"Central Europe Standard Time"` zone instead. Though in this case, there's no difference other than the `an` in the ID. Refer to [this mapping](http://unicode.org/repos/cldr/trunk/common/supplemental/windowsZones.xml). – Matt Johnson-Pint Nov 02 '16 at 19:59
  • Wow, thank you! It is a little bit weird, but actually makes sense with your explanation. And another big thanks for the `"Central Europe Standard Time"`, I bow before your knowledge. :) – vacip Nov 02 '16 at 20:55