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

I am adding screen shots.

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

I am adding screen shots.

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

Related Posts