Calculate the last day of the prior quarter
If @Date has the date in question
Select DateAdd(day, -1, dateadd(qq, DateDiff(qq, 0, @Date), 0))
EDIT: Thanks to @strEagle below, simpler still is:
Select dateadd(qq, DateDiff(qq, 0, @Date), -1)
Last day of most recent financial quarter in Snowflake
Was not sure if you want the last day of current quarter or prior quarter, but this should be enough to get where you want to go:
SELECT current_date today,
date_trunc('quarter', today) as cur_qrt,
dateadd('day', -1, cur_qrt) as last_day_of_prior_qrt,
dateadd('day', -1, dateadd('quarter', 1, cur_qrt)) as last_day_of_current_qrt;
TODAY | CUR_QRT | LAST_DAY_OF_PRIOR_QRT | LAST_DAY_OF_CURRENT_QRT |
---|---|---|---|
2022-01-19 | 2022-01-01 | 2021-12-31 | 2022-03-31 |
Get last day of previous quarter
This should work for VBA. It just determines the first day of the current quarter and then subtracts one day.
Debug.Print DateAdd("q", DatePart("q", Date) - 1, "1/1/" & Year(Date)) - 1
End of previous quarter / last day of previous quarter
Here are some possibilities with functions from packages zoo
and timeDate
, and base
R. The zoo
code was improved by @G.Grothendieck, and he also suggested the base
alternative (thanks a lot!). I leave the lubridate
solution(s) to someone else.
First, use class
yearqtr
in package zoo
to represent the quarterly data. You may then use as.Date.yearqtr
and the frac
argument "which is a number between 0 and 1 inclusive that indicates the fraction of the way through the period that the result represents. The default is 0 which means the beginning of the period" (see ?yearqtr
, and ?yearmon
for frac
).
Step by step:
library(zoo)
date <- as.Date("2014-11-03")
# current quarter
current_q <- as.yearqtr(date)
current_q
# [1] "2014 Q4"
# first date in current quarter
first_date_current_q <- as.Date(current_q, frac = 0)
first_date_current_q
# [1] "2014-10-01"
# last date in previous quarter
last_date_prev_q <- first_date_current_q - 1
last_date_prev_q
# [1] "2014-09-30"
And a short version by @G.Grothendieck (thanks!)
as.Date(as.yearqtr(date)) - 1
# [1] "2014-09-30"
A nice base
R solution by @G.Grothendieck
as.Date(cut(date, "quarter")) - 1
# [1] "2014-09-30"
Another possibility is to use timeFirstDayInQuarter
and timeLastDayInQuarter
functions in package timeDate
:
library(timeDate)
timeLastDayInQuarter(timeFirstDayInQuarter(date) - 1)
# GMT
# [1] [2014-09-30]
excel formula for previous quarters
To return the quarter | year corresponding to 3 months ago, i.e. :
"previous quarter & year"
simply replace col H date references with edate(date,-3)
. Cell J17 formula to enter is then:
="Q"&ROUNDUP(MONTH(EDATE(H17,-3))/3,0)&" "&YEAR(EDATE(H17,-3))
If you have Office 365 compatible version of Excel, you could shorten (albeit ever so slightly in this case) with the let
function as so:
=LET(x,EDATE(H17,-3),"Q"&ROUNDUP(MONTH(x)/3,0)&" "&YEAR(x))
How to get previous -previous quarters month and year in SQL Server
If it is the last day of the quarter then here is one solution. This should work going forwards and backwards.
DECLARE @ReportDate DATETIME = '2020-07-14'
DECLARE @QuarterInterval INT = -2
DECLARE @EndOfQuarter DATETIME =(SELECT DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0,DATEADD(QUARTER,@QuarterInterval , @ReportDate)) +1, 0)))
SELECT
TheDate = @EndOfQuarter,
TheMonth = DATEPART(MONTH,@EndOfQuarter),
TheYear = DATEPART(YEAR,@EndOfQuarter)
Find next quarter end date given previous quarter end date using Java
To answer your question, I think you are looking for this :
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yy");
LocalDate end = LocalDate.parse("30-09-20", formatter)
.plusMonths(3) // add three months to your date
.with(TemporalAdjusters.lastDayOfMonth()); // with the last day of the month
Note: don't use the legacy Date library, you tagged your question Java-8 which mean you can use java-time API.
Get last day of current quarter
@deHaar have reason, to get the end date of curent quarter, I would suggest to use :
public LocalDate lastDayFromDateQuarter(String date) {
final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yy");
LocalDate ld = LocalDate.parse(date, formatter);
int quarter = ld.get(IsoFields.QUARTER_OF_YEAR); // Get the Quarter, 1, 2, 3, 4
// Then create a new date with new quarter * 3 and last day of month
return ld.withMonth(quarter * 3).with(TemporalAdjusters.lastDayOfMonth());
}
Get last day of next quarter
To get the last day of the next quarter, then you just can add three months to your date like so :
public static LocalDate lastDayFromDateQuarter(String date) {
final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yy");
LocalDate ld = LocalDate.parse(date, formatter);
int quarter = ld.get(IsoFields.QUARTER_OF_YEAR);
return ld.withMonth(quarter * 3)
.plusMonths(3)
.with(TemporalAdjusters.lastDayOfMonth());
}
Related Topics
How Use Inserted\Deleted Table in Stored Procedure
MySQL Is Array in Multiple Columns
Documentdb SQL with Array_Contains
Reverse- Geocoding: How to Determine the City Closest to a (Lat,Lon) with Bigquery SQL
How to Improve This Mailing Address SQL Server Select Statement
How to Turn Off Implicit Type Conversion in SQL Server
SQL Server 2008: Bulk Datatype Change
SQL Server 2005 Restore One Schema Only
Want a Stored Procedure for Comma Seperated String Which Is of a Column (Has 20000 Rows ) in a Table
Select Last Records from Table Using Group By
Why Can't I Refer to a Column Alias in the Order by Using Case
Concatenate Multiple Rows in One Field in Access
Re-Use Aliased Field in SQL Select Statement
Postgresql - Order by an Array
How to Use User Defined Table Type Inside Another User Defined Table Type in SQL
Self-Referencing Constraint in Ms SQL