Click here to Skip to main content
14,332,142 members

How to convert UTC time to Local Time

Rate this:
1.50 (2 votes)
Please Sign up or sign in to vote.
1.50 (2 votes)
30 Dec 2011CPOL
Store your date time values in UTC format and convert it to local time when you present it to the user.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralRe: You are absolutely right and thank you very much for your co... Pin
Panayotis Matsinopoulos30-Dec-11 10:47
memberPanayotis Matsinopoulos30-Dec-11 10:47 
GeneralRe: My point is that if you have now 2 hours offset from UTC, th... Pin
bezjakr30-Dec-11 9:28
memberbezjakr30-Dec-11 9:28 
GeneralAs far as I am aware, the T-SQL function getdate() gives the... Pin
dnomyar9-Jan-12 12:55
memberdnomyar9-Jan-12 12:55 
GeneralReason for my vote of 2 Conversion should normally be done i... Pin
Philippe Mori3-Jan-12 2:36
memberPhilippe Mori3-Jan-12 2:36 
GeneralReason for my vote of 1 Wrong! Do it on GUI not on the serve... Pin
filoteanuadrian3-Jan-12 2:23
memberfiloteanuadrian3-Jan-12 2:23 
GeneralSince GETDATE() and GETUTCDATE() return current time (let sa... Pin
bezjakr30-Dec-11 0:07
memberbezjakr30-Dec-11 0:07 
GeneralRe: Here is a select that can help you understand it: &lt;pre> ... Pin
Panayotis Matsinopoulos30-Dec-11 0:28
memberPanayotis Matsinopoulos30-Dec-11 0:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 29 Dec 2011

Tagged as

Stats

43.9K views
7 bookmarked