Calculate the Last Day in the CURRENT quarter

Question

What's the most efficient way to calculate the last day of the CURRENT quarter?

Example: given the date 3/5/09, I want to return 3/31/09.

Platform is ColdFusion and SQL Server

Propman

@Learning: What made you think that the question needs and [sqlserver] tag? looks a bit puzzled – Cortezcortical

@Cortezcortical : because it says so in the question? :) – Handler

Do you want to do the last actual day, or the last business day of the quarter? – Calchas

Answer 1

SELECT     DATEADD(qq, DATEDIFF(qq, - 1, '3/5/09'), - 1) 

Handler

Answer 2

This answer uses the built in Quarter and DaysInMonth functions:

#createDate(year(now()), (quarter(now())*3), daysInMonth(createDate(year(now()), quarter(now())*3,1)) )#

It might be easier to read if its broken out a bit.

---

EDIT (@Sam Farmer: I took the liberty to transform your suggestion into a CF function)

<cffunction name="LastOfQuarter" returntype="date" output="no" access="public">
  <cfargument name="d" type="date" required="no" default="#Now()#">

  <cfset d = CreateDate(Year(d), Quarter(d) * 3, 1)>
  <cfreturn DateAdd("d", d, DaysInMonth(d) - 1)>
</cffunction>

Noncombatant

I thought about posting this in an answer of my own. But essentially it would have been a cheap rip-off of your idea, so I decided against it. ;-) – Cortezcortical

@Sam Farmer: Removed your edit (<cfset var ...>) and rolled back to original version. A local variable is unnecessary, really. :-) The function will write to "arguments.d", which won't hurt. – Cortezcortical

@Tomalak: If the function is used in a CFC, the variable "d" will not go into the arguments scope, and instead will go into the CFC-global "Variables" scope. That's why it's always best to use var for function-local variables. – Steinmetz

Answer 3

This could use some refactoring, but should get the basic idea across.

<cffunction name="lastDayOfQuarter">
   <cfargument name="d" default="#now()#">
   <cfif month(d) lte 3>
     <cfreturn createDate(year(d),03,31)>
   </cfif>
   <cfif month(d) lte 6>
      <cfreturn createDate(year(d),06,30)>
   </cfif>
    <cfif month(d) lte 9>
      <cfreturn createDate(year(d),9,30)>
   </cfif>
   <cfreturn createDate(year(d),12,31)>
 </cffunction>

Ansell

+1 for being efficient (which is what was requested), even though it is not necessarily elegant. – Rodney

Answer 4

declare @date date = '20230110'; 
select 
    dateadd(DD, 1, eomonth(@date,-(month(@date)-1)%3-1)) datestart, 
    DATEADD(QQ, 1,  eomonth(@date,-(month(@date)-1)%3-1)) datefinish

Theorist

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach? – Colorant

Answer 5

To get the quarter from a specific date:

SELECT dateadd(dd,-1,dateadd(qq,1,DATEADD(qq, DATEDIFF(qq,0,<date here>), 0)))

Or if you mean the current quarter:

SELECT dateadd(dd,-1,dateadd(qq,1,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)))

Vogt