Microsoft KB Archive/47048

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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

  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 query words: Transact-SQL

Keywords: kbprb KB47048