Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 5 down vote accepted

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:


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

share|improve this answer
Bingo - I can't believe I overlooked this. Thanks!! – JHFB Aug 2 '12 at 11:23

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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