SQL Programming: Convert UTC Time to Local Time

Most of todays enterprise software’s get data gathered from workstations located various parts of the world that run in different time zones. To ensure the data integrity and proper processing of events/information, software’s rely on saving the date  data in UTC and then convert them at run time for reporting.

When you have to run queries manually, you need to take care of this note and as well need to convert the date data into your local time and here is a quick trick that can help you.

[code language=”sql”]
–You can calculate the offset from UTC easily enough:

select DATEDIFF(hour,GETUTCDATE(),GETDATE())

–Which can be used in DATEADD as the amount to add.

SELECT complaintid,
DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()),incidentdt)
FROM complaint

[/code]

.