Date time data type resulted in out of range value

1

I have a varchar column with max length of 14
Characters I’m trying to convert it to date time
Using select convert(date time, stuff(stuff(stuff(updated,13,0,’:’). ,12,0,’:’),9,0,’ ‘)) from emp.

This works on other columns with the same length of 14 but on the updated column it errors out saying date time data type resulted in out of range value

2

Please try to see if there is any
"differences"
between Updated Column and the Other Columns ..
that will give clue

example .. data difference ... updated column 123 other column 124

3

try the following on all of your data set, in order to find the offending row(s)

;with src
as
(
SELECT   emp.updated,
    CASE WHEN TRY_CONVERT(datetime, stuff(stuff(stuff(updated,13,0,':'). ,12,0,':'),9,0,' ')) IS NULL   
    THEN 1  
    ELSE 0
END AS Failed
from emp
)

select * From src where Failed = 1
4

If you look at the character positions begin stuffed, I'm thinking there's a pretty good typo here. Since when are the seconds only 1 character from the hours?