DATEDIFF Overflow error

Question

DATEDIFF Overflow error

HelpMeInOhio

Hall of Fame

Points: 3945
More actions
January 4, 2005 at 8:39 am

#87365

select SSN, Dateborn, DateDied, DATEDIFF(day, Dateborn,DateDied) as NO_OF_DAYS from MyTable
where dateborn > ddatedied
When I run the above SQL I get and overflow error on the datediff. Any idea what I am doing wrong? the dates are formatted 'YYYYMMDD'
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

magasvs SSCertifiable Points: 7660 More actions · Answer 1

DATEDIFF works with datetime data type. What is you data type for columns Dateborn,DateDied? If not datetime - convert it first.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

In addition to Svetlana, do you have any default values define for Dateborn, DateDied in your table?

Posting the DDL might be a good idea.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 3

Dates aren't date format ... they are text. If I might ask a stupid questions, what would the convert solution be.

Thanks for all your great and fast feedback. I'm one of those DBAs by default and sometimes fall more than I stand.

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 4

Try looking up the cast/convert function in BOL. Try doing a little reading every once in a while before posting questions for things that can easily be found in the BOL

A.J.
DBA with an attitude

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Based on your sample, this should do:

select SSN, Dateborn, DateDied, DATEDIFF(day, CAST(Dateborn AS DATETIME),CAST(DateDied AS DATETIME)) as NO_OF_DAYS from MyTable

where CAST(dateborn AS DATETIME) > CAST(datedied AS DATETIME)

But it looks as though you have some bad date data in there anyway. You can check this with the ISDATE() function, which is explained in BOL. and correct it.

Any reasons why you don't store this data not in DATETIME columns?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 6

Thanks for the answer Frank! I do get into the bol as the other person mentioned but most times I find that this forums real work experience feel back is more valuable than the books. I have done a CAST before but wanted to see how you (pro's) might do it.

I inherited the database design and this is how they set it up. I'll look for the bad dates before I run again.

Thanks for taking the time to answer the question. Your reply was very helpful.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Glad it helps.

Hey, I'm still in smilie mood, since I go home now.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]