How to use dateadd function with passing parameter

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to use dateadd function with passing parameter

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-04-21 : 04:49:55

Hi all, how to update the Next_Run_Dt using DateAdd function and base on the Schedule_Type and Schedule_Value?


select * from tbl_EXE_Setting

App Area Schedule_Type Schedule_Value Next_Run_Dt
--- ---- ------------- -------------- -------------
1 1 mm 1 2009-04-01 00:00
1 2 mm 1 2009-04-01 00:00

This works,
Update tbl_EXE_Setting Set Next_Run_Dt=DateAdd(mm,1,Next_Run_Dt)

This returns error,
Update tbl_EXE_Setting Set Next_Run_Dt=DateAdd(Schedule_Type,Schedule_Value,Next_Run_Dt)

Please advise. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 04:56:10

[code]Update tbl_EXE_Setting
Set Next_Run_Dt = case Schedule_Type
when 'mm' then DateAdd(month, Schedule_Value, Next_Run_Dt)
when 'dd' then DateAdd(day, Schedule_Value, Next_Run_Dt)
else DateAdd(month, 0, Next_Run_Dt)
end[/code]

E 12°55'05.63"
N 56°04'39.26"

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-04-21 : 05:28:20

quote:

Originally posted by Peso

Update	tbl_EXE_Setting
Set	Next_Run_Dt = case Schedule_Type
			when 'mm' then DateAdd(month, Schedule_Value, Next_Run_Dt)
			when 'dd' then DateAdd(day, Schedule_Value, Next_Run_Dt)
			else DateAdd(month, 0, Next_Run_Dt)
		end

E 12°55'05.63"
N 56°04'39.26"

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources