Sql Server Managment DateTime OverFlow d, -100000, get datetime error-CodePudding

Hey i got no clue i asked few friends that use same program as me but they cant help me solve this issue i try run my server on hp proliant d380 Gen6 i got iLo( idk if that will cause issue or anything ) the copy of it works fin on Gen2 no iLo so cant say much anyway my error -

dbo.ents.containerId From dbo.Ents WHERE LastActive > DATEADD(d, -100000, GETDATE ()); SQLERROR: 517 22007 [Microsoft][SQL Server Native Client 11.0][SQL Server] Adding a Value to a 'datetime' column caused an overflow

not sure what cause it i tested to reinstall the program even other version of it and reinstalled the Ws2016 too if i did something else with the date / time on the computer itself ( my thoughts )

so now im here seeking help here. i seen some topics here BUT i dont know so much about this program beside add DB ( edit basic stuff in the db files ) , run query when i add SQL file or edit something within the DB i have hehe

CodePudding user response：

The problem is in DATEADD(d, -100000, GETDATE ()); function.

-100000 is out of range. You have to pass a number bigger than -100000 to the DATEADD() function.

for example -10000, this where clause expersion works:

LastActive > DATEADD(d, -10000, GETDATE ());

Take a look at the following link, its useful:

DATEADD SQL function introduction and overview

CodePudding user response：

Getdate() returns a datetime datatype. Adding your value to it throws an error since the result exceeds the domain of the datatype. As the others have noted, this logic is suspicious but only you know the reason for using such an odd increment. And while i'm at it:

DATEADD(d, -100000, GETDATE ())

How much effort did you save by typing "d" rather than "day"? Which is more readable? Laziness leads to bad habits and difficult to read/understand code.

To fix this particular error (but perhaps add another slight issue), you could use sysdatetime():

with cte (LastActive) as (select cast('20000102' as datetime) )
select * from cte 
where LastActive > DATEADD(day, -100000, sysdatetime());

select DATEADD(day, -100000, sysdatetime());

Sysdatetime() returns datetime2 which is more accurate than datetime and has a much larger domain (starting at 0001-01-01). Your datetime column will be implicitly "upconverted" to datetime2 due to datatype precedence for the comparison. I'll leave it to you to figure out what happens when you compare those two different datatypes and the effect of the additional precision. It might be irrelevant for your situation but it is something to acknowledge.