sql server - Convert local time in XML element to UTC - Stack Overflow

admin管理员组

文章数量:1406942

I need to convert a timestamp element in my xml column to UTC time in SQL Server 2019.

CREATE TABLE TestTable
   (    
       Id INT IDENTITY(1,1) NOT NULL,
       Data XML NOT NULL
   );

INSERT TestTable (Data) VALUES ('<root>
        <id>1</id>
        <timestamp>2024-08-20T10:55:37+10:00</timestamp>
    </root>');

I want to change the timestamp to "2024-08-20T00:55:37Z". I'm playing with "Data.modify('replace value of' " but can't find an example where the new value is based on the old value and not a simple string replacement.

I need to convert a timestamp element in my xml column to UTC time in SQL Server 2019.

CREATE TABLE TestTable
   (    
       Id INT IDENTITY(1,1) NOT NULL,
       Data XML NOT NULL
   );

INSERT TestTable (Data) VALUES ('<root>
        <id>1</id>
        <timestamp>2024-08-20T10:55:37+10:00</timestamp>
    </root>');

I want to change the timestamp to "2024-08-20T00:55:37Z". I'm playing with "Data.modify('replace value of' " but can't find an example where the new value is based on the old value and not a simple string replacement.

• sql-server
• xml
• t-sql

Share Improve this question Follow edited Mar 7 at 9:23 Thom A 96.3k1111 gold badges6161 silver badges9595 bronze badges asked Mar 7 at 8:47 MikeCMikeC 311 bronze badge 5

• So what have you tried? If you're trying to update values RBAR (Row By Agonizing Row) then there's always sql:variable("@VariableName") function references that can be included in your XQuery. If you're doing things set-based then its counterpart is sql:column("ColumnName") that could reference a computed value from a CROSS APPLY. See: sql:variable() and sql:column() – AlwaysLearning Commented Mar 7 at 9:01
• You can fetch the value by data.value('root/timestamp[1]', 'nvarchar(max)') or something like that and then do your thing on it – siggemannen Commented Mar 7 at 9:06
• @siggermannen the value() method (which is case-sensitive, by the way) can also convert to more useful data types such as datetimeoffset that could then be trivally converted to the UTC time zone. – AlwaysLearning Commented Mar 7 at 9:08
• @AlwaysLearning yeah, true, but i wasn't sure his string was correctly typed and didn't feel invested enough to find out :) – siggemannen Commented Mar 7 at 9:12
• @AlwaysLearning Thanks. I'd rather do a set update as batch. I'll try the CROSS APPLY method as mentioned by you and Thom-a – MikeC Commented Mar 9 at 22:10

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

There's a few ways to do this. I use a couple of APPLYs to get the value of the timestamp from the XML and then change it to UTC with AT TIME ZONE. Then you can use modify to UPDATE the XML value by using sql:column:

UPDATE TT
SET [Data].modify ('replace value of (/root/timestamp/text())[1] with sql:column("ts.utc")')
FROM dbo.TestTable TT
     CROSS APPLY TT.Data.nodes('root/timestamp') r(ts)
     CROSS APPLY (VALUES(r.ts.value('(./text())[1]','datetimeoffset(0)') AT TIME ZONE 'utc'))ts(utc);

This results in the following XML:

<root>
    <id>1</id>
    <timestamp>2024-08-20T00:55:37Z</timestamp>
</root>

Why not use a datetimeoffset for storage:

CREATE TABLE TestTable (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Data XML NOT NULL,
    TimestampUTC DATETIMEOFFSET NOT NULL
);

INSERT INTO TestTable (Data, TimestampUTC)
VALUES (
    '<root>
        <id>1</id>
        <timestamp>2024-08-20T10:55:37+10:00</timestamp>
    </root>',
    SWITCHOFFSET(CAST('2024-08-20T10:55:37+10:00' AS DATETIMEOFFSET), 0)
);

Than the query is faster as suggested with:

SELECT 
    Id, 
    Data, 
    CONVERT(NVARCHAR(50), TimestampUTC, 127) + 'Z' AS TimestampUTC_Formatted
FROM TestTable;

本文标签： sql serverConvert local time in XML element to UTCStack Overflow