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

Microsoft SQL Server 7.0

Microsoft SQL Server 6.5

Microsoft SQL Server 6.5 Service Pack 1

Microsoft SQL Server 6.5 Service Pack 1 and later

Microsoft SQL Server 6.5 Service Pack 2 and later

Microsoft SQL Server 6.5 Service Pack 3 and later

Microsoft SQL Server 6.5 Service Pack 4 and later

Microsoft SQL Server 6.5 Service Pack 5 and 5a

Microsoft SQL Server 6.5 Service Pack 5a

Microsoft SQL Server 7.0 Service Pack 1

Microsoft SQL Server 2000 (all editions)

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:
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

Modification Type:

Minor

Last Reviewed:

3/14/2005

Keywords:

kbprb KB47048