It is a good practice to store
datetime 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
datetime 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
my_table and a UTC stored
datetime column
created_on, you can do:
select dateadd(hour, datediff(hour, getutcdate(), getdate()), my_table.created_on) as local_time_created_on
from my_table;
This will present to you the
my_table.created_on values converted to your local time.
Explanation:
datediff with 1st argument
hour calculates the offset of your local time from UTC. It takes the distance of your current local time (
getdate()) from current UTC time (
getutcdate()) in hours. This offset is then added, with
dateadd, to the value that you want to convert.
Caution 1:
getdate() 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.
Caution 2: 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.
