Arithmetic overflow error converting expression to data type date time

Question

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.

Which dbms are you using? (Those functions are product specific.) — jarlh
– jarlh, Commented Sep 25, 2020 at 9:14
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. — Thom A
– Thom A ♦, Commented Sep 25, 2020 at 9:47

Gordon Linoff · Accepted Answer · 2020-09-25 10:52:48Z

0

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().

answered Sep 25, 2020 at 10:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wouter · Accepted Answer · 2020-09-25 09:46:01Z

0

Use the date functions:

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

answered Sep 25, 2020 at 9:46

Wouter

2,9763 gold badges11 silver badges22 bronze badges

3 Comments

Raja Over a year ago

Hi @wouter .I did this but I need to see my days in decimals too.But I am not getting that.

Wouter Over a year ago

Then do a DATEDIFF in hours and divide by 24 , or difference in minutes, seconds, etc. depending on how precise you want it.

Wouter Over a year ago

Btw if you want to use your convertToUnixTime function, then you should convert both datetimes (the getdate() and the CREATEDATE). But that will be much less efficient.

GMB · Accepted Answer · 2020-09-25 10:19:16Z

0

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

answered Sep 25, 2020 at 10:19

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting expression to data type date time

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related