Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Convert SQL Server DateTime Data Type to DateTimeOffset Data Type


By:   |   Last Updated: 2011-02-23   |   Comments (9)   |   Related Tips: More > Dates

Problem

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?

Solution

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)
VALUES (GETDATE())
GO

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
GO
-- 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,
       DateTimeOffsetExample
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,
       DateTimeOffsetExample,
       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


Last Updated: 2011-02-23


next webcast button


next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Janet,

AFAIK what you need is available only on SQL Server 2016 through the "AT TIME ZONE" hint (https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql).

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 https://time.is/ET, https://time.is/EST, and https://time.is/EDT

 


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

 

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')

See https://docs.microsoft.com/en-us/sql/t-sql/functions/todatetimeoffset-transact-sql

 


Friday, October 07, 2016 - 4:44:17 PM - Janet Kay Back To Top

 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.

Thanks.


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

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

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

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 

<
    SELECT TOP 10
    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

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.

Regards,
John


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

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

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..


Learn more about SQL Server tools