Convert varchar into datetime zone

sdh2000 0
Viewed 22

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

enter image description here

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?

datetime sql-server sql
1 Answers

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;
usertqF0
Related