Convert SQL Server DateTime Data Type to DateTimeOffset Data Type

By:   |   Comments (9)   |   Related: > Dates


I saw this tip on new date types provided with SQL Server 2008. I am interested in using the datetimeoffset because we support end users around the world. How do I convert the datetime data types to the datetimeoffset data type in SQL Server 2008?


Converting data in a datetime column to datetimeoffset is straight forward, but it will probably give you an unexpected conversion. By default the time zone offset is zero. Unless all your data resides in Iceland or the other countries with a time zone of UTC-0 this is going to be a problem.

The following example shows you how to convert your time through a set of scripts.

Note: DateTimeOffset is a new feature in SQL Server 2008. If your instance of SQL Server is before SQL Server 2008 this code will not work.

Step 0 - Create an example table and insert sample date time values as shown in the script below.

CREATE TABLE dbo.DateTimeDemo
(DateTimeExample DATETIME, -- current date type
 DateTimeOffsetExample DATETIMEOFFSET) -- new date type
-- Insert current date to convert to datetimeoffset
INSERT INTO dbo.DateTimeDemo (DateTimeExample)

Step 1 - We will convert the DateTimeExample column to DateTimeOffset data type and store it in the DateTimeOffsetExample column. You will notice that the offset is zero. This is the default. Unless your data resides in Iceland or another country with a time zone of UTC-0 this is going to be a problem. We will show you this step because we want to make sure you don't do this in your conversion. We will correct the time zone a little later in the demo.

UPDATE dbo.DateTimeDemo
SET DateTimeOffsetExample = DateTimeExample -- time zone = 00:00
-- Let's look at our conversion. You should see (+00:00) time zone 
SELECT DateTimeExample, DateTimeOffsetExample
FROM dbo.DateTimeDemo

You should see something similar to the screenshot provided below.

new date types provided with sql server 2008

Step 2 - For this example we will assume that the data resides on the east coast. We will convert the data to East Standard Time. This is done by converting the data to a varchar and appending the time zone value.

UPDATE dbo.DateTimeDemo
SET DateTimeOffsetExample = 
          CONVERT(varchar(50), DateTimeExample, 120)+ ' -5:00'
SELECT DateTimeExample,
FROM dbo.DateTimeDemo

You should see something similar to the screenshot provided below.

convert the datetime data types to the datetimeoffset data type in sql server 2008

Step 3 Now that the DateTimeOffset is stored with the correct time zone lets show the time to the user as local time. In this example we will assume that the application us running by a user in Central Standard Time. We will use the SWITCHOFFSET function to change the time.

SELECT DateTimeExample,
       SWITCHOFFSET (DateTimeOffsetExample , '-06:00')
       AS DateTimeSavingsOffsetExample
FROM dbo.DateTimeDemo

You should see something similar to the screenshot provided below. Notice that the time shown for the DateTimeSavingsOffsetExample column is in Central Standard Time (1 hour behind Eastern Standard Time) and the offset changed by one hour.

if you have users in different times zones you can manage the time zone specific date/times
Next Steps
  • If you have users in different time zones, consider this tip as a means to manage the time zone specific date\times.
  • Experiment with the code from this tip based on the time zones your applications support to see what sorts of code changes would be necessary.
  • Check out these related tips:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author John Sterrett John Sterrett is a DBA and Software Developer with expertise in data modeling, database design, administration and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, January 11, 2018 - 8:30:33 AM - Daniel Liuzzi Back To Top (74943)


AFAIK what you need is available only on SQL Server 2016 through the "AT TIME ZONE" hint (

If you can't use SQL Server 2016, you can use the CLR:

    var est = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
    var offsetFeb = est.GetUtcOffset(new DateTime(2015, 2, 1)); // -05:00:00
    var offsetApr = est.GetUtcOffset(new DateTime(2015, 4, 1)); // -04:00:00

Minor nitpick: you probably meant "standard ET" and "daylight savings ET". The "S" in EST literally means "Standard", so "standard EST" is redundant and "daylight savings EST" makes no sense. In sum:

EST = Eastern Standard Time
EDT = Eastern Daylight Time
ET = Eastern Time (EST or EDT, depending on which is currently observed)

See,, and


Thursday, January 11, 2018 - 7:46:25 AM - Daniel Liuzzi Back To Top (74942)


SQL Server already has a built-in way of doing this very thing: TODATETIMEOFFSET(datetime, time_zone), so you can skip the intermediate varchar conversion altogether. In other words:

Rather than doing this:

    CONVERT(varchar(50), DateTimeExample, 120)+ ' -5:00'

You can just do this instead:

    TODATETIMEOFFSET(DateTimeExample, '-05:00')



Friday, October 7, 2016 - 4:44:17 PM - Janet Kay Back To Top (43511)

 When converting historical datetime values the offset will be different for different date ranges. 

A date in Feb 2015 will have an offset of -5 (to convert UTC to EST).

A date in April 2015 will have an offset of -4 (to convert UTC to EST).

The offset is -5 during standard EST, -4 during daylight savings EST.

How do you resolve this when converting a table containing datetime values going back to 2014 to DateTimeOffset? I obviously don't want to store them all with today's offset (which is currently -4 on 10/7/2016 because we are currently in daylight savings time). I want to store each datetime with the correct offset at that point in time, not with today's offset.


Sunday, March 29, 2015 - 2:26:30 AM - Satori Back To Top (36740)

Thanks - this helped me convert UTC times to PST before inserting to the database.

Tuesday, December 18, 2012 - 2:21:02 AM - Gayathri Back To Top (20995)

Am having orders/purchase  table. am having date and rate column i want to compare that date using getdate() and i have to retrieve the rate related to that date in sql....

Any help for me....


Wednesday, August 29, 2012 - 12:03:33 PM - Dul Back To Top (19280)

The query below displays the information specifically the time is returned using the DB time how can this be change so it displays the local system time that way if the server is in Central time zone it displays Central time 

    dbo.AlertView.AlertStringName AS 'Alert Name',
    COUNT(dbo.AlertView.Id) AS 'Count',
    Max(dbo.AlertView.TimeRaised) as 'Last Alert'

    FROM AlertView
    Where MonitoringClassId   IN (
    SELECT ManagedType.ManagedTypeId
    FROM ManagedType
    LEFT OUTER JOIN BaseManagedEntity
    ON ManagedType.ManagedTypeId = BaseManagedEntity.BaseManagedTypeId
    WHERE ManagedType.TypeName LIKE '%WebApplication%'
    AND BaseManagedEntity.IsDeleted = 0)
    GROUP BY dbo.AlertView.AlertStringName
    ORDER BY Count(Id) DEsc


Sunday, February 27, 2011 - 9:16:41 AM - John Sterrett Back To Top (13056)

Hi h_d_t,

If you are using C# or VB.NET there is code built in you can use to determine if the the time is daylight or standard time. I will crank out a tip soon to show this.


Thursday, February 24, 2011 - 2:34:08 AM - chandra Back To Top (13026)

In step 2 you are hardcoding EST with -5, which should be automatically calculated based on user's locale.

Wednesday, February 23, 2011 - 5:30:04 PM - h_d_t Back To Top (13021)

what about daylight savings :(.... where timezones get a +1 or -1 during certain months of the year. and it does not always change on the same day each year, so you'll need a historical table of DST changes, and apply those when setting back to UTC..

get free sql tips
agree to terms