Microsoft KB Archive/47048

= PRB: DATEADD Doesn't Work When Using a Variable as a Date =

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 &quot;Msg 242, Level 16, State 0:,&quot; stating that an out of range conversion was attempted.



WORKAROUND
This problem can be corrected by ensuring the variable is correctly declared as datetime.



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:

Msg 242, Level 16, State 0:

  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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.