[SOLVED] Convert local time in XML element to UTC

Convert local time in XML element to UTC

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.

Solution

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>