x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 339

Previous month date calculations

Why following works for Feburary but not for april dates?  When @StarteDate is 4/15/13 and @EndDate is 4/30/13, @LPEndDate returns as '3/30/13' (it should return '3/31/13')? Any ideas?

declare       @NoOfMonths int,
@LPStartDate datetime,
@LPEndDate datetime,
@StartDate datetime = '2/15/13',
@EndDate datetime = '2/28/13'

set @NoOfMonths=DATEDIFF(M,@StartDate,@EndDate)+1

if datepart(d,@LPEndDate) >15 and datepart(d,@LPEndDate) <29
begin
end
select @LPStartDate, @LPEndDate
0
theone1
• 2
• 2
• 2
1 Solution

I already have a solution for you in my blog post here, check it out
http://everysolution.wordpress.com/2011/08/25/how-to-find-the-last-day-of-the-month/
0

the only flaw in your logic over here is.. you are checking if the date is  < 29, if that is corrected then the expected comes out ...

declare       @NoOfMonths int,
@LPStartDate datetime,
@LPEndDate datetime,
@StartDate datetime = '2/15/13',
@EndDate datetime = '2/28/13'

--set @StartDate = '4/15/13'
--set @EndDate = '4/30/13'

set @StartDate = '3/15/13'
set @EndDate = '3/31/13'

set @NoOfMonths=DATEDIFF(M,@StartDate,@EndDate)+1

if datepart(d,@LPEndDate) >15 and datepart(d,@LPEndDate) <=31
begin
end
select @LPStartDate, @LPEndDate
0

Author Commented:
Strange when I first had @LPEndDate <=30 it didnt work,,,,, but hey,,,, you are the one to solve it for me,,,, THANK YOU!!!
0

Commented:
my only contribution here will be - why look for the last day of a month?

this is a guess, but what this often stems from is someone using this date arithmetic for use in a date range filter
e.g.
select <<whatever>>
from <<whereever>>
where some_date between {first day of period} and {last day of period}

while this can be workable, often a simpler - AND more reliable method is to avoid using 'between', like so:

select <<whatever>>
from <<whereever>>
where some_date >= {first day of period}
and some_date <   {first day of next period}

i.e. it's quite easy to get the first day of any month (it's always 1)
and by using <   {first day of next period}
we don't have to concern ourselves with times during the {last day of month}

forgive me if this is utterly irrelevant here - it's a topic of interest to me.
0

Author Commented:
hey P Paul, thank you for explanation.... makes much sense :) you are good teacher sir!
0

Commented:
:) too kind

if you need more on this topic please see: "Beware of Between"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.