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.

Read about TRY_CONVERT. You can do ISNULL(TRY_CONVERT(.. first style), TRY_CONVERT(... second style) — siggemannen, Mar 10 '23 at 08:28
I used the `ISNULL` function together with `TRY_CONVERT`, but according to `ISNULL` syntax just two parameters are specified, the expression to be checked (`TimeStamp_UTC` in my case) and the replacement value (the conversion function). Nevertheless, your idea helped me finding a solution for the problem, since using just one `CONVERT` function for both datetime cases works. Thanks @siggemannen! — Jose Mari Muguruza, Mar 10 '23 at 10:04
I don't think you understood what i ment. The first agrument to ISNULL is the TRY_CONVERT call that tries the first style, and then second is the second TRY_CONVERT call that tries the second style. Since try_convert returns NULL on failures, you can do both styles with help of ISNULL — siggemannen, Mar 10 '23 at 10:09

score 2 · Accepted Answer · answered Mar 10 '23 at 09:11

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

score 1 · Answer 2 · answered Mar 10 '23 at 10:10

1

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)))

answered Mar 10 '23 at 10:10

Jose Mari Muguruza

23
7

SQL Server update same column using different conversion without overwriting

2 Answers2