SQL Server update same column using different conversion without overwriting

Question

I have a table DATES that looks like this:

Id	TimeStamp	TimeStamp_UTC
1	2021-08-03 12:10:30	2021-08-03 12:10:30.000
2	2021-09-13 12:21:44	NULL
3	11/23/2021 1:30:56.511 PM	NULL
4	11/23/2021 1:37:27.476 PM	NULL

The column TimeStamp is of type nvarchar and TimeStamp_UTC of type datetime.

I want to convert the data from TimeStamp into the column TimeStamp_UTC by using just one query.

That implies using two CONVERT functions, one for the first two dates and a slightly different one for the last two.

The SQL statements to convert both types are shown here:

For the first two:

UPDATE DATES
SET [TimeStamp_UTC_JM] = (SELECT CONVERT(datetime, [TimeStamp], 20) 
WHERE LEN([TimeStamp]) IN (18, 19))

For the other two dates:

UPDATE DATES 
SET [TimeStamp_UTC_JM] = (SELECT CONVERT(datetime, [TimeStamp], 21) 
WHERE LEN([TimeStamp]) BETWEEN 23 AND 26)

Individually both updates work, but when running the second query the converted values of the first update disappear, so I would like to perform the update in just one step, without overwriting.

Answer 1

Just run these two UPDATE statements after each other - check to ensure you're not overwriting any existing values in Timestamp_UTC in your WHERE clause:

-- update the first style of date/time formats
UPDATE dbo.Dates
SET [TimeStamp_UTC] = CONVERT(DATETIME2(3), [TimeStamp], 120) 
WHERE LEN([TimeStamp]) IN (18, 19)
  AND TimeStamp_UTC IS NULL;

-- update the second style of date/time formats
UPDATE dbo.Dates
SET [TimeStamp_UTC] = CONVERT(DATETIME2(3), [TimeStamp], 101) 
WHERE LEN([TimeStamp]) BETWEEN 23 AND 26
  AND TimeStamp_UTC IS NULL;

I had to also change the styles used for conversion - since you have 4-digit years, those have to be styles in the 100er range - and the second style you had doesn't exist in 4-digit years - but style = 101 seems to work just fine.

I also used DATETIME2(3) as datatype, since this is recommended since the days of SQL Server 2008 - it has a better precision, uses less memory to store its values, and has a larger range of supported dates - basically nothing but benefits over using the old DATETIME datatype. I'd strongly recommend defining any new columns to store date and time with the DATETIME2(n) datatype and phase out DATETIME

Answer 2

Another solution that worked in my case was, as I said, using just one CONVERT function for both datetime cases:

UPDATE DATES
SET TimeStamp_UTC = (SELECT ISNULL(TimeStamp_UTC, CONVERT(datetime, [TimeStamp], 21)))