Add one day to parameter

[simma]

Hello,
How do I add a day to parameter in stored procedure.For example,I declared

@start varchar(10),
@finish varchar(10)
when selecting parameters I would like to select with one day extra from finish.i.e finish+1
my select looks like

(SELECT max(payment_history.date_paid) as ms
FROM payment_history
WHERE payment_history.date_paid
between @start and @finish)
How do i add oneday to finish
I tried @finish +1 but gives error cannot convert datetime to string

Please advice
Thanx

 

[ReportsMan]

I suggest using the Dateadd() function - the function adds the number of hours/days/months etc (see your SQL help)

The query would then look like:

SELECT
max(payment_history.date_paid) as ms
FROM payment_history
WHERE payment_history.date_paid between
@start and dateadd(dd,1,@finish)

Hope this helps!

 

[sunila7]

Hi,

Since u have used @Start and @finish as varchar's u r getting that problem. may be u can declare it as datetime.

@start datetime,
@finish datetime

Set @finish = DateAdd(dd,1,@finish)
(SELECT max(payment_history.date_paid) as ms
FROM payment_history
WHERE payment_history.date_paid
between @start and @finish)



Sunil

 

[simma]

Thank you guys!I tried this but its giving me some records a year back if i give 30 in date.
Any Advice?
Thanx

 

[sunila7]

Hi,

i didnt understand... can u put the code which u r trying to execute...

Sunil

 

[simma]

oh Thanx Sunil!I figured it out something to do with my code.How do i give an if statement to add a day
i.e
if @finish is getdate()
then
set @finish=.......

thanx

 

[sunila7]

hi
I think u can, do something like this

if DATEDIFF(dd, @finish , getdate()) = 0 -- iam assuming that u r checking wether the finish date is today then add 1 to it
Begin
Set @Finish = = DateAdd(dd,1,@finish)

End


Sunil

 

[simma]

Excellent,it works
Thans sunil!!

 

[simma]

Excellent,it works
Thanx sunil!!