0

We use a PLM software called "Aras Innovator" and need to store a date as a custom item property. The software uses Microsoft SQL Server to store its data and accessing properties on the database level is officially supported.

However, our issue is that the software only supports datetime, not date. And dates are stored in UTC - so when we store "2020-01-01" from a client in Central Europe, it becomes "2019-12-31 23:00" or so in the database (1 hour before midnight on the day before). And queries checking for ">= 2020-01-01" then consequently fail to find these rows.

The software manufacturer says:

There is no standard feature for storing only the month and day; a quick option may be to store it as a string instead and do programmatic validation on the inputted string if required.

Regarding direct SQL queries, all dates are stored in UTC for compatibility with multiple time zones. They are automatically converted when accessed via the standard API. When accessing direct with SQL, you can use the function ConvertToLocal described in the Aras Innovator 11.0 - Configuring Internationalization guide on the CD Image, section 5.3:

SELECT item_number, innovator.ConvertToLocal(created_on, 'Eastern Standard Time') AS CreatedOn FROM innovator.Document

ConvertFromLocal can be used for specifying dates you wish to query against rather than requiring those dates to be sent in UTC.

I'm not convinced that working with string would be a good solution. But not convinced that their "ConvertTo/FromLocal" functions would be a good solution either. Surely there must be some other way to handle this directly in SQL Server?

TravelingFox
  • 484
  • 5
  • 18
  • 1
    I think your supplier's solution is correct, and is the best possible implementation for dealing with data that spans timezones. If you stored the date only, you would essentially be masking the timezone issues you are grappling with, and you potentially have at least 24 versions of 'today' to deal with. UTC date and time keys all your dates and times to 1 timezone, and ConvertToLocal allows you to determine where 'today' is on any given query. – Thomas Steven Apr 09 '19 at 08:36
  • 1
    The thing is that we really only care about dates, globally. For us it does not make sense to work with timezones. Imagine something like a birthday calendar for a large corporation (just to give a simple example) and you want to find all employees born on April 9, no matter in which timezone they were born or what the user's current timezone is. Or all employees born after May 5, 1980. Introducing timezones just creates a mess. – TravelingFox Apr 09 '19 at 12:07

1 Answers1

0

SQL Server 2016 introduced the AT TIME ZONE statement.

Consider:

SELECT CONVERT(date, TheDateTime AT TIME ZONE 'UTC'
                                 AT TIME ZONE 'Central Europe Standard Time')

The above will first convert TheDateTime field from a datetime or (datetime2) to a datetimeoffset type with zero offset (UTC). Then it will convert it to the Central Europe Standard Time zone, and lastly will convert it to a date type, removing any time information.

As far as your statement:

... >= 2020-01-01" then consequently fail to find these rows.

You should consider instead converting the opposite direction such that you are querying against UTC time - which would be sargable. For example:

SELECT ...
WHERE TheDateTime >= CONVERT(datetime,
                     '2020-01-01' AT TIME ZONE 'Central Europe Standard Time'
                     AT TIME ZONE 'UTC')

Of course, you could do the whole right-side of the expression ahead of time and use a local variable instead. Likewise, if you are calling this query from an application, you could convert to UTC in your application code and not have to bother doing it in SQL Server at all.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Thanks. That's why using a string to store the dates seemed like a terrible idea to me. Or would string comparison be sargable? However, what I find deeply worrying is the idea that we might be inserting dates from a timezone other than CET. Say we want to insert "2020-02-02" from a machine that's on Tokyo time (UTC+9). Most likely, the date would be stored as "2020-02-01 15:00:00" (UTC). That would be a complete disaster. The only alternative would be to always create dates as midnight UTC time - but I'm not sure if that's possible as dates are "automatically converted" (see above). – TravelingFox Apr 10 '19 at 09:57
  • Best idea would be to store as a `date` type in SQL, rather than a `datetime`. But since it sounds like you can't do that, store dates at noon instead of midnight. Noon will never switch dates on conversion (unless you have customers in the Line Islands (UTC+13, UTC+14)). – Matt Johnson-Pint Apr 10 '19 at 15:14
  • I think we will go with saving the data as string. Slower results are better than incorrect results. – TravelingFox Apr 10 '19 at 17:37
  • 1
    If you do, then make sure you use the `YYYY-MM-DD` format always. Otherwise they won't be sortable. – Matt Johnson-Pint Apr 10 '19 at 19:30