Skip to main content

Data types

Our philosophy regarding the data we retrieve and process is to always present it as accurately and faithfully to the original systems as possible. In certain well-considered cases, we deviate from this and make simplifications and corrections for usability where we believe that structures and data types are unnecessarily complex or overly expressive.

Date and Time​

One such instance where we aim to facilitate the handling and interpretation of the retrieved data concerns date and time. At the same time, we strive to be as precise as possible and avoid misunderstandings.

In general, if a data attribute contains a date along with a time, it should always be represented by a data type that specifies the time zone for the timestamp to avoid ambiguity. Similarly, if an attribute only contains timestamps that are 00:00:00, we assume that the attribute is intended to represent only a date. In these cases, we perform conversions to the more appropriate data type.

Note

For attributes that only contain dates, we use the SQL data type date, and in cases where a timestamp with a time zone is present, we use the datetimeoffset data type.

User tips for handling data types with time zone​

If at any point it's necessary to reconvert an attribute with a time zone data type (datetimeoffset) back to date or datetime for compatibility reasons, here are some tips on how to easily accomplish that.

Display specific time zone for the datetimeoffset column
SELECT <COLUMN_WITH_DATETIMEOFFSET_DATA_TYPE> AT TIME ZONE 'Central European Standard Time'
FROM <TABLE_NAME>
Convert from datetimeoffset to date
SELECT CONVERT(date, <COLUMN_WITH_DATETIMEOFFSET_DATA_TYPE> AT TIME ZONE 'Central European Standard Time')
FROM <TABLE_NAME>
Convert from datetimeoffset to datetime2
SELECT CONVERT(datetime2, <COLUMN_WITH_DATETIMEOFFSET_DATA_TYPE> AT TIME ZONE 'Central European Standard Time')
FROM <TABLE_NAME>

Replace <COLUMN_WITH_DATETIMEOFFSET_DATA_TYPE> with the database column of type datetimeoffset that needs to be converted, and also replace <TABLE_NAME> with the correct table name.

tip

In the examples above, the CEST time zone is used, but it is of course possible to use other time zones as the target for the conversion. See the system view SELECT * FROM sys.time_zone_info for a list of available time zone names.