ansaurus

Question

Calculate the Last Day in the CURRENT quarter

Answer 1

+6 A:

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

Learning 2009-03-05 17:44:17

Answer 2

+2 A:

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>

Patrick McElhaney 2009-03-05 17:44:27

lol @whoever was ignorant enough to down-vote this.

Tomalak 2009-03-05 17:47:18

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

Dane 2009-03-09 17:41:21

Answer 3

A:

select dateadd(dd,-1,dateadd(qq,1,DATEADD(qq, DATEDIFF(qq,0,), 0)))

actually if you mean current quarter: select dateadd(dd,-1,dateadd(qq,1,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)))

jhale 2009-03-05 17:44:33

Answer 4

+3 A:

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>

Sam Farmer 2009-03-05 17:52:51

Thanks! Nice looking function :)

Sam Farmer 2009-03-05 18:24:21

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

Tomalak 2009-03-05 18:37:52

@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.

Tomalak 2009-03-06 09:57:31

@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.

Adam Tuttle 2009-03-06 14:25:38

Answer 5

A:

Could you clarify if you want it to be the last business day or the last actual day of the quarter.

Ran into that once. It might be an additional step you need to calculate for.

Jas Panesar 2009-03-06 18:03:33

ansaurus

tags:

views:

answers:

Calculate the Last Day in the CURRENT quarter

related questions