cannot convert yyyymmdd to yyyy-mm-dd - arithmetic overflow error

Question

I am trying to do something that is very simple and am stumped why it is not working.

I have a column (sysjobschedules.next_run_date from msdb) that is a varchar formatted like yyyymmdd. I want to convert that to a datetime. I use the rather straight-forward command CONVERT(datetime,next_run_date,112) but I keep getting the error Arithmetic overflow error converting expression to data type datetime.

The three distinct values in this column are 20120802, 20120803, and 20120806. Using that same function above, I was able to successfully convert all three strings.

I feel like I must be missing something obvious...?

Answer 1

The column is actually stored as an int, and it means something different in datetime, so you'll need one extra step in your CONVERT:

CONVERT(DATETIME, CONVERT(CHAR(8), next_run_date), 112);

To show why you can't do this directly to an int:

SELECT CONVERT(DATETIME, 0);
SELECT CONVERT(DATETIME, 1);

When it's an int, it's actually the number of days since 1900-01-01.