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

• Microsoft SQL Server versions 7.0, 6.5, 6.5 Service Pack 1, 6.5 Service Pack 1 and later, 6.5 Service Pack 2 and later, 6.5 Service Pack 3 and later, 6.5 Service Pack 4 and later, 6.5 Service Pack 5 and 5a, 6.5 Service Pack 5a, 7.0 Service Pack 1

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

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
      

      declare @valdate datetime
      select @valdate=convert(datetime,'07/07/57')
      select dateadd(day,15,@valdate)
      go 

Additional query words: Transact-SQL

Keywords : kbtool SSrvBCP
Version : winnt:6.5,6.5 Service Pack 1,6.5 Service Pack 1 and later,6.5 Service Pack 2 and later,6.5 Service Pack 3 and later,6.5 Service Pack 4 and later,6.5 Service Pack 5 and 5a,6.5 Service Pack 5a,7.0,7.0 Service Pack 1
Platform : winnt
Issue type : kbprb