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) + " " +

CONVERT(char(2),DATEPART(dd, date)) + " "+

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.