CODEWITHSUNDEEP
sqlms-access
Wilskt
Wilskt

Reputation: 347

Access SQL - given two dates, return the dates of the previous period

I need to find the dates of the preceding time period - e.g. if the given months are Jan-13 to Mar-13, I need to be able to calculate that the previous period is Oct-12 to Dec-12. Or if the dates are Jul-12 to Jun-13, I need to come up with Jul-11 to Jun 12. So basically for a time period of x months, I need to return another time period of x months that finishes the month before the specified time period.

The date will always be the first of the month, so 1st June 2012 or 6/1/2012 or 1/6/2012 depending on where you are in the world.

This SQL query works, but seems to be causing problems later on when I incorporate it in a Left Join query (see existing question: Access 2007 - Left Join to a query returns #Error instead of Null)

SELECT DATEADD("m",
1-(1+MONTH(QueryDates.EndDate)-MONTH(QueryDates.StartDate)+
    (12*(YEAR(QueryDates.EndDate)-YEAR(QueryDates.StartDate)))),
DATEADD("m",-1,QueryDates.StartDate)) AS StartDatePrevious, 
DATEADD("m",-1,QueryDates.StartDate) AS EndDatePrevious
FROM QueryDates;

It looks a bit crazy from where I'm sitting, so is there a simpler way of achieving this task; or is there anything wrong in the SQL that could confuse Access later on?

Upvotes: 1

Views: 1199

Answers (2)

Tom Collins
Tom Collins

Reputation: 4069

Here's a little cleaner SQL. It assumes that the StartDate is always the first of the month.

This calculates the period length, and returns the same length ending the day before the current period starts.

SELECT QueryDates.StartDate, 
       QueryDates.EndDate, 
       Dateadd("m", Datediff("m", [EndDate] + 1, [StartDate]), [StartDate]) AS 
       StartDatePrevious, 
       [StartDate] - 1                                                      AS 
       EndDatePrevious 
FROM   QueryDates;

Upvotes: 0

SoLongDentalPlan
SoLongDentalPlan

Reputation: 156

How does this work for you? This is, I believe, the same thing you're doing in your query, but using the DateDiff function makes it look less gnarly. DateDiff tells you that (for example) there are 2 months between 1/1/2013 and 3/1/2013. Therefore we are looking at a 3-month span, so we subtract 3 months from both the start date and the end date to get the previous span:

SELECT DateAdd("m",DateDiff("m",EndDate,StartDate)-1,StartDate) AS StartDatePrevious
,DateAdd("m",DateDiff("m",EndDate,StartDate)-1,EndDate) AS EndDatePrevious
FROM QueryDates;

By the way, unless I'm mistaken, I believe you intended to say "...I need to be able to calculate that the previous period is Oct-12 to Dec-12..."

Upvotes: 1

Related Questions