Access SQL-给定两个日期，返回上一个期间的日期 [英] Access SQL - given two dates, return the dates of the previous period

问题描述

我需要找到前一个时间段的日期-例如如果给定的月份是13年1月13日至3月13日，则我需要能够计算出前一个时期是10月12日至12月12日.或者，如果日期是7月12日至6月13日，我需要得出7月11日至6月12日.因此，基本上在x个月的时间段内，我需要返回另一个x个月的时间段来完成该月在指定时间段之前.

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.

日期将始终是该月的第一天，因此2012年6月1日或2012年6月1日或2012年6月1日将取决于您在世界上的位置.

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.

此SQL查询有效，但稍后在将其合并到Left Join查询中时似乎会引起问题(请参阅现有问题:)

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;

从我坐着的地方看起来有点疯狂，所以有没有更简单的方法可以完成此任务?还是SQL中有什么错误会使以后的Access感到困惑?

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?

推荐答案

这对您有何作用?我相信，这与您在查询中所做的相同，但是使用DateDiff函数使它看起来不那么粗糙. DateDiff告诉您(例如)从1/1/2013到3/1/2013之间有2个月.因此，我们正在考虑3个月的跨度，因此我们从开始日期和结束日期中减去3个月即可得出前一个跨度:

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;

顺便说一句，除非我弄错了，否则我相信您打算说:"...我需要能够计算出上一个时期是 10月12 到 Dec -12 ..."

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..."

这篇关于Access SQL-给定两个日期，返回上一个期间的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！