Getting next month's Data based on parameter field

[luvtitans50]

I have 2 parameters I am passing in and they are being used as below:

@start = '2010-03-01'
@end = '2010-03-31'.

sum(case when o.startdate between @start and @end then originalopenings else 0 end) as newopenings,

now I need to create another formula but this time I want the ones with start date for the next month based on the current parameter values being passed in?
--o.startdate between '2010-04-01' and '2010-04-30'

How can I incorporate that into a formula?

 

[genomon]

You could use DateAdd() to increment the variables.

Beir bua agus beannacht!

 

[luvtitans50]

I can increment @start as dateadd(mm,1,@start) and get the correct value, but how do I get the end of the next month considering that march has 31 days and April has 30 days?

That is where my issue comes in at, figuring out the formula for @end?

Thanks,

 

[markros]

Month correctly increments by one month.

declare @EndDate date = '2010-03-31'
select @EndDate, DATEADD(month,1, @EndDate)

PluralSight Learning Library

 

[genomon]

Ask, and you shall receive!

http://wiki.lessthandot.com/index.php/How_to_find_the_first_and_last_days_in_years,_months_etc

Beir bua agus beannacht!

 

[luvtitans50]

If I enter 4/30/2010 as @end the value returned is 5/30/2010 and there are 31 days in May?

 

[genomon]

Which solution are you referring to?

Beir bua agus beannacht!

 

[markros]

Did you check the link above?

PluralSight Learning Library

 

[IanWaterman]

Just add 2 months and subtract one day

dateadd(mm,2,@start)-1

Ian