# Arithmetic overflow error converting expression to data type int(How to convert 18 digits integer to datetime)

SQL Server error: Arithmetic overflow error converting expression to data type int
SQL Server error: Arithmetic overflow error converting expression to data type int

When I executed query below on SSMS I got error like Arithmetic overflow error converting expression to data type int

`select dateadd(second, 132706767376983975 /1000 + 8*60*60*60, '19700101')`

When I executed query below on SSMS I got error like Arithmetic overflow error converting expression to data type int

`select dateadd(second, 132706767376983975 /1000 + 8*60*60*60, '19700101')`

`132706767376983975?`
`1,626,249,414, can you tell us what this enormous number represents?`

I managed to get something remotely sensible using this calc, which rounds up to minutes:

`DECLARE @Input BIGINT = 132706767376983975 / 10000000 SELECT @Input = @Input / 60 select dateadd(minute, @Input, CAST('1601-01-01' AS DATETIME2(7)))`

2021-07-13 19:05:00.0000000

This isn’t quite the same as the correct answer though (powershell)

`[datetime]::FromFileTime("132706767376983975")`

Wednesday, 14 July 2021 5:05:37 AM

But I’m guessing this has to do with time zones.

The `dateadd` function expects to add an integer value as parameter.

Integer values are from `-2,147,483,648` to `2,147,483,647`, and your value is quite bigger than this, even divided by `1000`.

If you want to use the `dateadd` function for this operation, you will need to split the add operation in several ones, all of them inside the allowed integer values, or use another datepart different than seconds.

You are watching: Arithmetic overflow error converting expression to data type int(How to convert 18 digits integer to datetime). Info created by THVinhTuy selection and synthesis along with other related topics.

Rate this post