ID Number: Q47048
1.10 1.11 4.20
OS/2
Summary:
SYMPTOMS
The use of a variable as a date in the DATEADD command can result
in an error message of "Msg 242, Level 16, State 0:", stating
that an out of range conversion was attempted.
RESOLUTION
This problem can be corrected by ensuring the variable is
correctly declared as datetime.
More Information:
The following steps reproduce this problem:
1. Issue the following query in ISQL:
declare @valdate char(10)
select @valdate=convert(datetime,'07/07/57')
select dateadd(day,15,@valdate)
go
2. The following error message is returned:
Msg 242, Level 16, State 0:
3. The conversion of CHAR to DATETIME results in a DATETIME value out
of range. Arithmetic overflow also occurs. However, for the given
values, there should be no arithmetic overflow. In fact, the
following query executes correctly:
select dateadd(day,15,'07/07/57')
go
This can be corrected by changing step 1 as follows:
declare @valdate datetime
select @valdate=convert(datetime,'07/07/57')
select dateadd(day,15,@valdate)
go
Additional reference words: Transact-SQL