Menu
What's the most efficient way to calculate the last day of the prior quarter?
Example: given the date 11/19/2008, I want to return 9/30/2008.
Platform is SQL Server
577
Select a blank cell which next to the date, here I select C1, and type this formula =ROUNDUP(MONTH(A1)/3,0) into it, then press Enter key to get the relative quarter. Tip: A1 is the date you need to get quarter from, and you can change it to your need.
You can return the last day in the month by using the EOMONTH function. Cell "A1" displays a date. All these cells have been formatted with the custom number format "dd dddd mmmm yyyy".
In SQL Server, you can use DATEPART(QUARTER,whn) and YEAR(whn) . In Oracle, you can use TO_CHAR(whn,'Q') and TO_CHAR(whn,'YYYY') for the quarter and year. In PostgreSQL, you can use EXTRACT(QUARTER FROM whn) and EXTRACT(YEAR FROM whn) . In Access, you can use DatePart("q", whn) and YEAR(whn) .
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)
Actually simpler is:
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), -1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
