Solved

Previous month date calculations

Posted on 2013-06-18
6
330 Views
Last Modified: 2013-06-19
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

set @LPEndDate=DATEADD(m,-1,@EndDate)
set @LPStartDate =DATEADD(m,@NoOfMonths*-1,@StartDate)
if datepart(d,@LPEndDate) >15 and datepart(d,@LPEndDate) <29
begin
set @LPEndDate=DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@EndDate),0)))
end
select @LPStartDate, @LPEndDate
0
Comment
Question by:theone1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39257417
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
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 250 total points
ID: 39257428
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

set @LPEndDate=DATEADD(m,-1,@EndDate)
set @LPStartDate =DATEADD(m,@NoOfMonths*-1,@StartDate)

if datepart(d,@LPEndDate) >15 and datepart(d,@LPEndDate) <=31
begin
set @LPEndDate=DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@EndDate),0)))
end
select @LPStartDate, @LPEndDate 

Open in new window

0
 

Author Closing Comment

by:theone1
ID: 39257455
Strange when I first had @LPEndDate <=30 it didnt work,,,,, but hey,,,, you are the one to solve it for me,,,, THANK YOU!!!
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39258233
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 Comment

by:theone1
ID: 39261322
hey P Paul, thank you for explanation.... makes much sense :) you are good teacher sir!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39261369
:) too kind

if you need more on this topic please see: "Beware of Between"
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help how to find where my error is in UFD 6 47
GeoClustering  and AOG 25 43
Disable TLS1.0 on Win 2012 server 7 64
selective rebuild of SQL Tables in scheduled job 10 38
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question