Arithmetic overflow error converting expression to data type date time

0
votes

I am trying to get days difference between 2 days and I am getting below error. Could any one please help

CONVERT(DECIMAL,((dbo.convertToUnixTime(GETDATE())-p.CREATEDDATE)))

Msg 8115, Level 16, State 2, Line 79

Arithmetic overflow error converting expression to data type datetime.

3
sqlsql-server
Which dbms are you using? (Those functions are product specific.) - jarlh
Hi @jarlh I am using sql server - Raja
CREATEDDATE data type? - jarlh
@jarlh its datetime - Raja
What if the definition of dbo.convertToUnixTime? Why are you defining your value as just decimal, where the precision and scale? Why do you appear to be subtracting a date away from another? Dates aren't numbers, it doesn't make sense to "subtract" 2020-09-17T19:12:59.462 from 2020-09-25T10:47:04.123. - Larnu

3 Answers

0
votes

Use the date functions:

SELECT DATEDIFF(day,GETDATE(),p.CREATEDATE)
0
votes

datediff() always return an integer value, expressed in the unit your are giving as first argument.

If you want something accurate, you can compute the date difference in seconds, then use artithmetics to convert to a number of days:

datediff(second, p.createdate, getdate()) / 60.0 / 60 / 24
0
votes

If you want fractional days, then you can take the difference in another unit, say seconds, and divide:

SELECT DATEDIFF(second, p.CREATEDATE, GETDATE()) / (24.0 * 60 * 60)

Note that for DATEDIFF() the older date goes first. I assume p.CREATEDATE is in the past and you want a positive number.

If you are dealing with dates far in the past or the future, you might want to use minute rather than second or alternatively DATEDIFF_BIG().