Arithmetic overflow error converting expression to data type datetime error in sql server

Question

I am trying to calculate date diff and getting below error.

Arithmetic overflow error converting expression to data type datetime.

Below is my code

select (CONVERT(DECIMAL,((dbo.convertToUnixTime(GETDATE())-tr.created))))/86400
from Allreporting  tr

@Serg 2018-11-20 17:25:25.000 is the format of created. Created is a datetime column — Krish
– Krish, Commented Sep 4, 2019 at 8:50
@Serg I want to get days open from the above query.So using datediff from current date to create date — Krish
– Krish, Commented Sep 4, 2019 at 8:52
can you show us value of dbo.convertToUnixTime(GETDATE()) please — Ven
– Ven, Commented Sep 4, 2019 at 8:56
Hi @Ven 1567587455 is the value of dbo.convertToUnixTime(GETDATE()) — Krish
– Krish, Commented Sep 4, 2019 at 8:58

Serg · Accepted Answer · 2019-09-04 08:59:52Z

1

You want datediff Demo:

select datediff(dd,cast ('2018-11-20 17:25:25.000' as datetime) /* tr.created*/, getdate()) days

answered Sep 4, 2019 at 8:59

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

1 Answer 1