I've had to do this so many times over the years, for all sorts of different time frames, so I decided to build, document and share my algorithms. Using ColdFusion code for the examples, the following demonstrates how to obtain the start and finish dates within the time periods:
- Yesterday
- Today
- Last Week
- This Week
- Last Month
- This Month
- Last Quarter
- This Quarter
- Last Year
- This Year
<!--- YESTERDAY --->
<cfoutput><p>Yesterday:
#DateFormat(DateAdd("d",-1,NOW()), "yyyy-mm-dd")# —
#DateFormat(DateAdd("d",-1,NOW()), "yyyy-mm-dd")#</p></cfoutput>
<!--- TODAY --->
<cfoutput><p>Today:
#DateFormat(NOW(), "yyyy-mm-dd")# —
#DateFormat(NOW(), "yyyy-mm-dd")#</p></cfoutput>
<!--- LAST WEEK --->
<cfset startDate = DateFormat(DateAdd("d",-(DayOfWeek(NOW()) + 6),NOW()), "yyyy-mm-dd")>
<cfoutput><p>Last Week:
#startDate# —
#DateFormat(DateAdd("d",6,startDate), "yyyy-mm-dd")#</p></cfoutput>
<!--- THIS WEEK --->
<cfoutput><p>This Week:
#DateFormat(DateAdd("d",-(DayOfWeek(NOW())) + 1,NOW()), "yyyy-mm-dd")# —
#DateFormat(NOW(), "yyyy-mm-dd")#</p></cfoutput>
<!--- LAST MONTH --->
<cfset finishDate = DateFormat( DateAdd("d", -1, Year(NOW()) & "-" & Month(NOW())) & "-01" , "yyyy-mm-dd")>
<cfoutput><p>Last Month:
#Year(finishDate) & "-" & Month(finishDate) & "-01"# —
#finishDate#</p></cfoutput>
<!--- THIS MONTH --->
<cfoutput><p>This Month:
#Year(NOW()) & "-" & Month(NOW()) & "-01"# —
#DateFormat(NOW(), "yyyy-mm-dd")#</p></cfoutput>
<!--- LAST QUARTER --->
<cfset lastQuarter = Ceiling(Month(Now()) / 3) - 1>
<cfif lastQuarter eq 0>
<cfset startDate = CreateDate(Year(Now()) - 1, 10, 1)>
<cfelse>
<cfset startDate = CreateDate(Year(Now()), (lastQuarter - 1) * 3 + 1, 1)>
</cfif>
<cfoutput><p>Last Quarter:
#DateFormat(startDate, "yyyy-mm-dd")# —
#DateFormat(DateAdd("d", -1, DateAdd("m", 3, startDate)), "yyyy-mm-dd")#</p></cfoutput>
<!--- THIS QUARTER --->
<cfset thisQuarter = Ceiling(Month(Now()) / 3)>
<cfoutput><p>This Quarter:
#Year(NOW()) & "-" & ((thisQuarter - 1) * 3 + 1) & "-" & "01"# —
#DateFormat(NOW(), "yyyy-mm-dd")#</p></cfoutput>
<!--- LAST YEAR --->
<cfoutput><p>Last Year:
#(Year(NOW()) - 1) & "-01-01"# —
#(Year(NOW()) - 1) & "-12-31"#</p></cfoutput>
<!--- THIS YEAR --->
<cfoutput><p>This Year:
#Year(NOW()) & "-01-01"# —
#DateFormat(NOW(), "yyyy-mm-dd")#</p></cfoutput>