Calculating a prior date.

I need to calculate a prior date. When I pass a negative dayvalue this fails
.
AUCTION_DATE is a smalldatetime column in a table, and I am sure you can
imagine what the from and where clauses look like.
Select DATEADD(day, 100, AUCTION_DATE) - works but is of course incorrect
Select DATEADD(day, -100, AUCTION_DATE) - Fails
Select AUCTION_DATE -100 - fails
Server: Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated.
So, how does one calculate a prior date with T-Sql'It turns out that AUCTION_DATE contains '1900-01-01' in some cases.
Apparently you can not do date arithmetic on a SmallDateTime if the result
preceeds '1900-01-01'. I was not aware that a smalldatetime can not store a
date prior to '1900-01-01' and of course this is a very common datatype in
our databases. I can hardly wait until these dates flow into our system!!!!
"Snake" wrote:

> I need to calculate a prior date. When I pass a negative dayvalue this fa
ils.
> AUCTION_DATE is a smalldatetime column in a table, and I am sure you can
> imagine what the from and where clauses look like.
> Select DATEADD(day, 100, AUCTION_DATE) - works but is of course incorre
ct
> Select DATEADD(day, -100, AUCTION_DATE) - Fails
> Select AUCTION_DATE -100 - fails
> Server: Msg 8115, Level 16, State 2, Line 4
> Arithmetic overflow error converting expression to data type smalldatetime
.
> The statement has been terminated.
> So, how does one calculate a prior date with T-Sql'
>|||Do something like that to prevent substracting from minimum dates:
Select CASE AUCTION_DATE
WHEN '1900-01-01'
ELSE DATEADD(day, -100, AUCTION_DATE)
END
Dont know how you store these dates so that would be the ISO syntax:
Select CASE CONVERT(varchar(8),AUCTION_DATE,112)
WHEN CONVERT(varchar(8),'1900-01-01' ,112)
ELSE DATEADD(day, -100, AUCTION_DATE)
END
HTH, Jens Suessmeyer.