Microsoft KB Archive/66818

PRSQL9012018: Can’t Subtract with DATEADD Function ID Number: Q66818

1.10 OS/2 buglist1.10

Summary:

PROBLEM ID: PRSQL9012018

SYMPTOMS When subtracting 1 from the year with the DATEADD function, the following error message is received:

Adding a value to a datetime field caused an overflow CAUSE The number added to the year datepart is less than zero. This problem is limited to the year datepart; the other dateparts work correctly.

RESOLUTION To perform this functionality, use both the DATEPART function and the CONVERT function, as demonstrated in the following query:

declare @test char(20) select @test = DATENAME(month, date) + &quot; &quot; + CONVERT(char(2),DATEPART(dd, date)) + &quot; &quot;+ CONVERT(char(4),DATEPART(yy, date)-1) from sales where stor_id = '7066' select @test Microsoft has confirmed this to be a problem in SQL Server version 1.1. Microsoft is researching this problem and will post new information here as it becomes available.