1

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...?

1 Answer 1

5

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.

1
  • Bingo - I can't believe I overlooked this. Thanks!!
    – JHFB
    Commented Aug 2, 2012 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.