It is a good practice to store
values in UTC time and use conversion to local time when you want to present it to the end user.
When you want to convert your SQL Server
values stored in UTC to your local time, you can use the following trick.
dateadd(hour, datediff(hour, getutcdate(), getdate()), <your_column_with_utc_time>)
For example, assuming that you have a table with name
and a UTC stored
, you can do:
select dateadd(hour, datediff(hour, getutcdate(), getdate()), my_table.created_on) as local_time_created_on
This will present to you the
values converted to your local time.
with 1st argument
calculates the offset of your local time from UTC. It takes the distance of your current local time (
) from current UTC time (
) in hours. This offset is then added, with
, to the value that you want to convert.
calculates the local time based on your system configuration (regional settings). If you want to convert to local time based on actual remote user accessing your data over, for example a web interface, then you will need to know the offset/time zone of that particular user. Then you cannot use this practice that I am presenting here. Unless, all these web interface users are located in the same region like your SQL Server system does and you assume that they have the same regional settings.
: Credits go to bezjakr
and I am very thankful for this comments. This solution does not take into account Daylight Saving. I will soon come back on that with possible approaches to cover Daylight Saving in the cases where this is possible.
Your comments are welcome.