DateTime to DateTimeOffset

5 pts.
Hi, we currently have a table with over a billion rows recorded with a datatype of datatime that spans across 6 years. We would like to change the datatype to date time offset but after experimenting with doing that it puts a 00:00 for the offset. Is there a way to do it and insert the correct offset for the data? Thanks BR

Answer Wiki

Thanks. We'll let you know when a new response is added.

I suppose this is on SQL Server.

If you expect to be able to add some offset when altering the column, then (I’m almost sure) that is not possible. The database will assign an offset of ’00:00′ because the offset is not included in the datetime type, so your data doesn’t contain an offset.

You would have to alter the column, and then update the data, for which you could use the SWITCHOFFSET function (but that would be a long operation on a table that size).




Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Chippy088
    Not knowing exactly why you would want to convert a date to an offset, makes a solution difficult to define. Made harder by the fact that you haven't mentioned any of the basic facts to take into consideration like What program was used stored the data. How big is/are the blocks of data you want to access. Will the integrity of the data be conpromised by breaking it down into managable chunks. Dates are stored as a number, it is only when we humans want to understand them that the formatting is used. (i.e. dd/mm/yyyy etc) If you want to convert all the dates to an offset of, say the very first date, then you could try something like this. date_offset = date_to_convert - base_date Notice I haven't made it language specific. If you import the dates into an unformatted excel sheet you might see the number which represents the date. If you have a billion dates to work on, bear in mind, excel will not work well under a load like that. You could write a VBA app to go through the list doing the convertions, (working on a copy of course,) and see how it goes. Sorry I can't give a more specific answer, but now you have an idea how it could be done, good luck.
    4,625 pointsBadges:
  • carlosdl
    The sofware being use was not mentioned, but as it mentions a 'table', a data type, and over a billion of rows, it is for sure referring to a database, and DateTimeOffset is a SQL Server 2008 data type. In this case, the offset is not a difference between a given date and a base date, but a Time zone offset, which ranges from -14:00 to 14:00.
    85,145 pointsBadges:
  • Kccrosser
    You could create the data with an offset (in a new column) by converting the datetime value to a string, appending a "known" offset, and then converting to a datetimeoffset datatype. declare @MyOldDateTime datetime declare @MyNewDTOffset datetimeoffset set @MyNewDTOffset = convert(datetimeoffset(convert(varchar(32),@MyOldDateTime,120) + '-09:00') This is pretty easy to do - unless your data was recorded in a location that uses Daylight Savings (which is most of the world). If you are in a DST area, you would need to take the old date/time, determine if it was within the DST date window, and add the offset accordingly. Also, for those records between 0200 and 0300 at the DST time change points, how would you know whether to add the Standard or Daylight offset? You will probably have to decide to either use DST after 0200 or after 0300 on those dates and live with the fact that some of the offsets will be incorrect for those windows. Also, don't forget that the date of change for DST changed itself a couple of years ago, so your six years of data contain records with the old DST date range as well as the new range. If I really had to do this, I would use a non-blocking cursor to find the records not yet converted (perhaps where the datetimeoffset column was null) and update chunks of records (500-1000) at a time to minimize rollback segment sizes and table locking issues.
    3,830 pointsBadges:
  • Kccrosser
    Doh - disregard the algorthm in the last post... As Carlosdl says, the SwitchOffset function can be used, and is a lot cleaner: declare @MyDateTime datetime declare @MyDTOffset datetimeoffset set @MyDTOffset = switchoffset(convert(datetimeoffset,@MyDateTime), '-09:00') Same general comments, but easier to apply. Obviously, you would need to figure out the appropriate offset to apply.
    3,830 pointsBadges:
  • tracker1
        ,datepart( tz, sysdatetimeoffset() )

    datepart(tz,sysdatetimeoffset())  gets you the local offset (assuming you used GETDATE() to insert the dates.  TODATETIMEOFFSET converts the column to a version with offset information.  you could use '+hh:nn' or '-nnn' formatting if the offset is known in your conversion.  I'm wrapping this in a SWITCHOFFSET( result, '+00:00' ) to convert to UTC based datetimeoffsets in my results.
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: