Article ID: 47048
Article Last Modified on 3/14/2005
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 6.5 Service Pack 1
- Microsoft SQL Server 6.5 Service Pack 1
- Microsoft SQL Server 6.5 Service Pack 2
- Microsoft SQL Server 6.5 Service Pack 3
- Microsoft SQL Server 6.5 Service Pack 4
- Microsoft SQL Server 6.5 Service Pack 5a
- Microsoft SQL Server 6.5 Service Pack 5a
- Microsoft SQL Server 7.0 Service Pack 1
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q47048
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.
WORKAROUND
This problem can be corrected by ensuring the variable is correctly declared as datetime.
MORE INFORMATION
Steps to Reproduce the Problem
Issue the following query in ISQL:
declare @valdate char(10) select @valdate=convert(datetime,'07/07/57') select dateadd(day,15,@valdate) go
- The following error message is returned:
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 query words: Transact-SQL
Keywords: kbprb KB47048