Convert varchar into datetime zone-CodePudding

I have a column called updated_at which has datetime zone in varchar.

I would like to alter and convert the column into datetime zone in SQL Server. I referred to other posts but with no success

This is a sample row:

2022-09-23T11:35:21.469 01:00

Can anyone advise the SQL script for the above to convert the column into datetime zone in the table?

CodePudding user response：

SQL Server supports converting ISO 8601 formatted date time values to datetimeoffset using CONVERT - you should also specify style: 127 (which corresponds to ISO 8601 formats) to ensure that SQL Server evaluates CONVERT in a deterministic fashion (for predictability, portability, and performance reasons).

To convert a column in an existing table you'll need to add a new NULL-able column first, populate it with the converted values and make it NOT NULL, then drop the old column. Use a TRANSACTION to be safe:

SET XACT_ABORT ON;

BEGIN TRANSACTION myTxn;

ALTER TABLE dbo.myTable
    ADD updated_at_dto datetimeoffset(7) NULL;

UPDATE
    dbo.myTable
SET
    updated_at_dto = CONVERT( datetimeoffset(7), updated_at_dto, 127 );

ALTER TABLE dbo.myTable
    ALTER updated_at_dto datetimeoffset(7) NOT NULL;

ALTER TABLE dbo.myTable
    DROP COLUMN updated_at;

EXEC sys.sp_rename @objname = N'dbo.myTable.updated_at_dto', @newname = 'updated_at', @objtype = 'COLUMN';

COMMIT TRANSACTION myTxn;