Search code examples
sqlsql-serverdateadd

Adding a value to a 'datetime2' column caused an overflow

I know why I'm seeing this error, it's because some of my locationDeletionDate is '9999-12-31 00:00:00.0000000', and adding 90 days to the locationDeletionDate (as I do in my query) causes the error in the title:

...WHERE                
(bpj.JobStatus = 'Live') 
AND (l.locationEffectiveDate <= SYSDATETIME()) 
AND (dateadd(d,90,l.locationDeletionDate) >= SYSDATETIME())...

I guess what I need is a conditional CASE in my WHERE-clause to ensure the date will not error if 90 days are added? Or is there a more elegant way?

Solution

  • You can use this approach to solve you issue

    ...WHERE                
(bpj.JobStatus = 'Live') 
AND (l.locationEffectiveDate <= SYSDATETIME()) 
AND (l.locationDeletionDate >= dateadd(d, -90, SYSDATETIME())...

    In this case you also solve a performance issue. Your predicate becomes SARGable.