Skip to main content
N
New_York_Guy
Known Participant
March 12, 2009
Question

Rolling twelve months

  • March 12, 2009
  • 2 replies
  • 445 views
I have a report that should always output twelve months worth of data based on the closed date.

So this is what I have. First, I define today date :
<cfset today = #dateformat(now(), "mm/dd/yyyy")#>

I then calculated one year ago, from today :
<cfset lastyear = #dateformat(datediff("d",365,today),"mm/dd/yyyy")#>

In my query, I have :
WHERE closedDate between #CreateODBCDate(lastYear)# and #CreateODBCDate(dateadd("d", 1, today))#

So everytime they run the report, it should give one year/tweleve months worth of data.

Is this the right way to do this, or is there a more efficient method ?

I tested and it seems to work, but my test data is very limited so I am not really sure if it is picking up the correct data or not.
    This topic has been closed for replies.

    2 replies

    -
    -__cfSearching__-
    Inspiring
    March 12, 2009
    > <cfset lastyear = #dateformat(datediff("d",365,today),"mm/dd/yyyy")#>

    I do not think that will create the correct date. Typically, you would use dateADD("yyyy", interval, someDate). Using a positive interval to add and a negative interval to subtract.

    Also, DateFormat returns a string - which CF then converts back into a date/time object. In most cases strings are interpreted as expected, but a safer bet is to return a non-ambiguous format like "yyyy-mm-dd".
    N
    Newsgroup_User
    Inspiring
    March 12, 2009
    New York Guy wrote:
    > In my query, I have :
    > WHERE closedDate between #CreateODBCDate(lastYear)# and
    > #CreateODBCDate(dateadd("d", 1, today))#

    That should return the expected data.

    > Is this the right way to do this, or is there a more efficient method ?

    You should explore your database management system's date functions. It
    may offer something that can off load this work to the database.

    If the data set is large, you will want proper indexes on the closedDate
    field so the database management system can efficiently sort through the
    data.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices