Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

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:

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

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

 
discard

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.