DATEADD - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DATEADD

Author

Topic

kurtgr
Starting Member

25 Posts

Posted - 2009-09-30 : 12:44:43

I am getting this error when i use the DATEADD function

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Below is the code i am using

Declare @DatePart varchar(5), @UnitsToAdd int,@EffectiveDate SMALLDATETIME

set @DatePart = 'month'
set @UnitsToAdd = 3
set @EffectiveDate = getDate()

declare @cmd nvarchar(255), @Parms nvarchar(255), @dt datetime

set @Parms = '@Units int,@Eff SmallDatetime, @dtOutput datetime OUTPUT'
set @cmd = 'set @dtOutput = Dateadd(' + @DatePart + ',@Units,'+''''+Convert(Char(10),@EffectiveDate)+''''+')'
exec sp_ExecuteSQL @cmd, @parms,@Units = @UnitsToAdd,@Eff = @EffectiveDate, @dtOutput = @dt OUTPUT

select @dt

Can someone please assist me in saying what am i do wrong

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-30 : 12:51:10

Why are you using dynamic SQL for this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

kurtgr
Starting Member

25 Posts

Posted - 2009-09-30 : 13:30:50

Cause when passing the variables to the function I get errors especially with the first parameter

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-30 : 13:35:22

Convert(Char(10),@EffectiveDate) isn't a date

convert(varchar(10),getdate(),112)

Jim

Everyday I learn something that somebody else already knew

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-30 : 13:47:31

As you found out you can't use an parameter (or expression) as the datepart element of DATEADD. But rather than use dynamic sql, one possibility is to use a CASE to get the new date.


select @dt = 
       case @datepart
       when 'month' then dateadd(month, @unitsToAdd, @effectiveDate) 
       when 'day' then dateadd(day, @unitsToAdd, @effectiveDate) 
       --etc...
       end

But besides Jim's observation you also would need to convert @units to varchar to concatenate the value into the @cmd string.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources