The Conversion of a varchar data type to datetime data type resulted in an out-of-range value
The data is stored in the database as col1 = 'yyyymmdd' and col2 = 'hh:mm' CAST(table.col1 + ' ' + table.col2 AS DATETIME) Produces the out of range error at some point. I know it is a date because CAST(col1 AS DATETIME) gives the error but CAST(col2 AS DATETIME) does not.
Tried CASE WHEN ISDATE(col1) = 1 THEN CAST(col1 AS DATETIME) ELSE col1 END but this results in the same error. Ideally I would like to see the problem value(s) if it cannot cast properly.
This is a simple select statement. I cannot do anything about the data except ignore it, convert it or show it as is.