Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Migrate SQL Server DateTime column to DateTimeOffset

Tags:

I have an old table with a few rows that has a datetime column. I want to switch this to datetimeoffset but I want to be able to transfer the data that already exists. So I'm doing something like:

SET IDENTITY_INSERT Table_Temp ON  INSERT INTO Table_Temp     (Col0, ... ColN,) SELECT      COl0,.... ColN, from  Table_Original;      SET IDENTITY_INSERT Table_Temp OFF 

This works but the offset set is 0 when I do the datetime to datetimeoffset assignment. Fortunately the offset that I want to set it to is the offset of the current system. I can't seem to figure out an easy way to do this.

I want to be able to set the offset within the conversion. I was going to resort to doing a C# utility(or PowerShell) but I would rather keep it simple.

like image 694
hdz Avatar asked Jan 05 '10 19:01

hdz


People also ask

Does SQL Server datetime store timezone?

SQL Server does not store time zone data when storing timestamps. It uses the host server time as the basis for generating the output of getdate() .

Should I use datetime2 or Datetimeoffset?

This depends on whether or not you need to include a time zone offset. If you need to include a time zone offset, then you'll need to use datetimeoffset. If not, then use datetime2, as you'll save storage space and eliminate any potential issues with having a (potentially wrong) time zone offset in your data.

How can I separate date and time from datetime in SQL Server?

Another way: SELECT TOP (10) [CUSTOMERID], [DATEPART] = CONVERT(date, [ENTRYTRIPDATETIME]), [TIMEPART] = CONVERT(time, [ENTRYTRIPDATETIME]) FROM [ISSUER].

What is datetime offset in SQL?

The DATETIMEOFFSET data type is a date and time with time zone awareness. DATETIMEOFFSET supports dates from 0001-01-01 through 9999-12-31. The default value is 1900-01-01 00:00:00 00:00. The time is based on 24-hour UTC clock. UTC = Universal Time Coordinate or Greenwich Mean Time.


2 Answers

See below for doc, you probably want something like:

-- up here set the @time_zone variable.  INSERT INTO Table_Temp     (Col0, ... ColN,) SELECT      COl0, TODATETIMEOFFSET(COLDATE, @time_zone),.... ColN, from  Table_Original; 

From MSDN

The SWITCHOFFSET function adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC value. The syntax is SWITCHOFFSET(datetimeoffset_value, time_zone). For example, the following code adjusts the current system datetimeoffset value to time zone GMT +05:00:

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');

So if the current system datetimeoffset value is February 12, 2009 10:00:00.0000000 -08:00, this code returns the value February 12, 2009 13:00:00.0000000 -05:00.

The TODATETIMEOFFSET function sets the time zone offset of an input date and time value. Its syntax is TODATETIMEOFFSET(date_and_time_value, time_zone).

This function is different from SWITCHOFFSET in several ways. First, it is not restricted to a datetimeoffset value as input; rather it accepts any date and time data type. Second, it does not try to adjust the time based on the time zone difference between the source value and the specified time zone but instead simply returns the input date and time value with the specified time zone as a datetimeoffset value.

The main purpose of the TODATETIMEOFFSET function is to convert types that are not time zone aware to DATETIMEOFFSET by the given time zone offset. If the given date and time value is a DATETIMEOFFSET, the TODATETIMEOFFSET function changes the DATETIMEOFFSET value based on the same original local date and time value plus the new given time zone offset.

For example, the current system datetimeoffset value is February 12, 2009 10:00:00.0000000 -08:00, and you run the following code:

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-05:00');

The value February 12, 2009 10:00:00.0000000 -05:00 is returned. Remember that the SWITCHOFFSET function returned February 12, 2009 13:00:00.0000000 -05:00 because it adjusted the time based on the time zone differences between the input (-08:00) and the specified time zone (-05:00).

As mentioned earlier, you can use the TODATETIMEOFFSET function with any date and time data type as input. For example, the following code takes the current system date and time value and returns it as a datetimeoffset value with a time zone -00:05:

SELECT TODATETIMEOFFSET(SYSDATETIME(), '-05:00');

like image 169
Hogan Avatar answered Sep 24 '22 07:09

Hogan


If you're using a version of SQL Server that knows of the datetimeoffset type, this syntax will work for getting you the local tz offset of the server:

select datepart(tz,sysdatetimeoffset())

The result is in MINUTES.

like image 42
Broam Avatar answered Sep 21 '22 07:09

Broam