Converting UTC to local time

Problem

Values in the database are stored as UTC date and time in a simple datetime format. You want to see data time values in the local time.

Solution

Use the following code to convert UTC date/time to local:

CREATE TABLE #table (date_UTC datetime)

/* Populate with UTC date time */

Insert #table Select GETUTCDATE()

Select

/* UTC value */

date_UTC

/* convert UTC to local time */

,DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),date_UTC) as date_In_LocalTime

From

table