October 26, 2012 at 11:27 am
Hi,
Can someone help me with this query? I have two columns both are INT and have dates stored in them as YYYYMMDD I need to use them to calculate age I've been trying to use DATEDIFF but I get the error Arithmetic overflow error converting expression to data type datetime.
Here's my query
SELECT DATEDIFF(hour,ArrivalDateID,BirthDateID)/8766 AS Age
FROM TableA
Looking at the error maybe DATEDIFF can't be used for INT fields? Or I need to convert it to dateTime first?
Thanks,
October 26, 2012 at 12:10 pm
scribesjeff (10/26/2012)
Hi,Can someone help me with this query? I have two columns both are INT and have dates stored in them as YYYYMMDD I need to use them to calculate age I've been trying to use DATEDIFF but I get the error Arithmetic overflow error converting expression to data type datetime.
Here's my query
SELECT DATEDIFF(hour,ArrivalDateID,BirthDateID)/8766 AS Age
FROM TableA
Looking at the error maybe DATEDIFF can't be used for INT fields? Or I need to convert it to dateTime first?
Thanks,
Yes you need to first convert your ints to datetime. This is a perfect example of why you should use proper datatypes. If you have date information store it as datetime. If at all possible change the column in the table to datetime. You are facing nothing but issues storing stuff in the wrong datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2012 at 12:15 pm
I would also point out that based on the calculation in your query you are possibly not going to get the data you want. Even if you first convert your int to datetime you are doing integer division which means you will get an integer result.
SELECT DATEDIFF(hour,ArrivalDateID,BirthDateID)/8766 AS Age
Let's just say that you have already converted to datetime and you run this query. If the result of the datediff is 23456 I suspect you would want an answer like 2.675792 (rounded to whatever decimal place you desire). With integer math this will be 2.
SELECT 23456/8766 AS Age
You can easily force this by adding a decimal point to your constant like this.
SELECT 23456/8766. AS Age
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2012 at 1:55 pm
I've had that problem before with a really bad database design.
I recommend you to change the type of that column to avoid more problems.
Now, the solution would be to cast your int to string and then to datetime.
Something like this
SELECT CAST( CAST( 20121026 AS char(8)) AS datetime)
I'm adding an extra column to show what the int value of the same date would be (very different to 20121026)
SELECT CAST( CAST( '20121026' AS datetime) AS int)
--Result
--41206
October 26, 2012 at 2:02 pm
Sean Lange (10/26/2012)
I would also point out that based on the calculation in your query you are possibly not going to get the data you want. Even if you first convert your int to datetime you are doing integer division which means you will get an integer result.
SELECT DATEDIFF(hour,ArrivalDateID,BirthDateID)/8766 AS Age
Let's just say that you have already converted to datetime and you run this query. If the result of the datediff is 23456 I suspect you would want an answer like 2.675792 (rounded to whatever decimal place you desire). With integer math this will be 2.
SELECT 23456/8766 AS Age
You can easily force this by adding a decimal point to your constant like this.
SELECT 23456/8766. AS Age
Maybe that's the result he wants. The age in complete years. For a person, the age is rarerly used with decimal values (maybe months or days).
However, SQL Server won't take in account the 6 hours per year that form a leap year. I'm sure there was a thread to find the best solution for age calculation.
October 26, 2012 at 2:29 pm
Luis Cazares (10/26/2012)
Sean Lange (10/26/2012)
I would also point out that based on the calculation in your query you are possibly not going to get the data you want. Even if you first convert your int to datetime you are doing integer division which means you will get an integer result.
SELECT DATEDIFF(hour,ArrivalDateID,BirthDateID)/8766 AS Age
Let's just say that you have already converted to datetime and you run this query. If the result of the datediff is 23456 I suspect you would want an answer like 2.675792 (rounded to whatever decimal place you desire). With integer math this will be 2.
SELECT 23456/8766 AS Age
You can easily force this by adding a decimal point to your constant like this.
SELECT 23456/8766. AS Age
Maybe that's the result he wants. The age in complete years. For a person, the age is rarerly used with decimal values (maybe months or days).
However, SQL Server won't take in account the 6 hours per year that form a leap year. I'm sure there was a thread to find the best solution for age calculation.
That is exactly why I said possibly. 😛
There have been a couple of threads on here to calculate age.
http://qa.sqlservercentral.com/articles/T-SQL/63351/[/url]
http://qa.sqlservercentral.com/Forums/Topic928705-392-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2012 at 6:22 pm
Thanks everyone I appreciate the help. I was able to link it back to a date table with proper datetime fields and do my datediff with that. The cast and convert always keep giving me the same error converting to datetime. And yes I agree about changing the data type to datetime but then someone else will complain its got the time stamp attached and they have to convert to remove it.
Thanks Again!
October 27, 2012 at 7:25 pm
In SQL Server 2008 they introduced the date type if you don't need the time.
Consider using it.
For earlier versions, you can always eliminate the time before storing it in the database.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply