Conversion failed when converting date and/or time from character string during update only

Question

I have been searching for a resolution for a long time now and just can't seem to formulate a query that brings back the resolution so as a last resort I have posted here. I have a SQL server table with a varchar column that has the date and time stored in this format

"1/1/2013 11:38:31 PM Some other text"

I needed this date and time portion of this data to be stored in another column in datetime datatype. So I created a new column called DateTimeLog of type datetime.

I then used left to chop off the extra text and convert to change the value to datetime format and got the result I would expect.

select CONVERT(DATETIME,(rtrim(left(olddate, 21)))) from mytable
results:
"2013-01-01 23:38:31.000"

So far, so good. this is what I would expect. My troubles begin when I attempt to update my new datetime column with the results of this CONVERT statement.

update mytable
SET DateTimeLog = CONVERT(DATETIME,(rtrim(left(olddate, 21)))) from mytable

I get the infamous "Conversion failed when converting date and/or time from character string" error message.

Conversion failed when converting date and/or time from character string.

I have also attempted to use cast

update mytable
SET DateTimeLog = (cast(CONVERT(DATETIME,(rtrim(left(oldtable, 21)))) as datetime)) from mytable

the error persists. As best I can tell the convert is working correctly because I can see the result set from a select, but getting that result into a new column has eluded me thus far. thanks,

Eric Brandt Eric Brandt · Accepted Answer · 2018-12-13T21:58:19

Your string isn't going to consistently be 21 characters long. Your sample data shows a single character month and a single character date. What if it's, say, 12/13/2018?

That said, you need a more robust way to isolate that timestamp. I used a PATINDEX to capture the position of the last colon in the time component, with a couple of regexes in there to account for the numbers & the AM/PM thing. Then I added 6 to it to get to the end of the string of interest.

This seems to work:

DECLARE @t TABLE (olddate VARCHAR(100));
INSERT @t
  (
    olddate
  )
VALUES
  ('12/13/2018 11:38:31 PM Some other text')
  ,('1/1/2018 11:38:31 PM Some other text');

SELECT CAST(LEFT(olddate,PATINDEX('%:[0-9][0-9] [AP]M%', olddate)+6) AS DATETIME)
FROM @t;

Results:

+-------------------------+
| 2018-12-13 23:38:31.000 |
| 2018-01-01 23:38:31.000 |
+-------------------------+

Rextester: https://rextester.com/BBPO51381 (although the date format's a little funky on the output).

Conversion failed when converting date and/or time from character string during update only

1 Answers