CODEWITHSUNDEEP
sqlsql-servercoldfusion
Margie
Margie

Reputation:

Calculate the Last Day in the CURRENT quarter

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

Upvotes: 5

Views: 6604

Answers (5)

Sasha Fedorov
Sasha Fedorov

Reputation: 11

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

Upvotes: 1

jhale
jhale

Reputation: 1830

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)))

Upvotes: 0

Sam Farmer
Sam Farmer

Reputation: 4118

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>

Upvotes: 4

Patrick McElhaney
Patrick McElhaney

Reputation: 59271

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>

Upvotes: 3

Learning
Learning

Reputation: 8185

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

Upvotes: 8

Related Questions