-1

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:

select dbo.ents.containerId
From dbo.Ents
Where LastActive > DATEADD(d, -100000, GETDATE ());

my error:

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

mohabbati
  • 1,162
  • 1
  • 13
  • 31
TheDarkNoise
  • 1
  • Does this answer your question? ["Adding a value to a 'datetime' column caused an overflow."](https://stackoverflow.com/questions/9401643/adding-a-value-to-a-datetime-column-caused-an-overflow) – GSerg Nov 28 '21 at 09:45
  • Can you explain what are you trying to do with that `WHERE` clause of the query ? – Squirrel Nov 28 '21 at 10:47
  • 100,000 days ago would be, roughly, 273 years ago; is that intended? A `datetime` only goes back to 1753, and 1748 was before then. If you need dates that go that far back, you're using the wrong data type. – Thom A Nov 28 '21 at 11:53

1 Answers1

0

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.

SMor
  • 2,830
  • 4
  • 11
  • 14