Count the no of days between 2 dates

ndeeley · #1 May 18th, 2009, 02:31 PM

So, how do I count the number of days between two dates?

I have set my variables to carry dates:

Expand|Select|Wrap|Line Numbers

 <cfset dtFrom = #CreateODBCDate(dateFormat(form.Pump_FromDate, 'dd/mm/yyyy'))# />

<cfset dtTo = #CreateODBCDate(dateFormat(form.Pump_ToDate, 'dd/mm/yyyy'))# />

<cfset dtDiff = (dtTo - dtFrom) />

But can't output the number of days between them as a whole number.
Can anyone help?

Thanks
Neil

acoder · #2 May 18th, 2009, 09:01 PM

Use the dateDiff() function.

ndeeley · #3 May 19th, 2009, 08:49 AM

Ta

Amazingly I did work this one out myself, BUT, now my client has moved the goalposts...

I have produced a report that pulls info from a database - and two dates. He now wants to calculate the dateDiff between these two dates, for every record in the database (these have to be working days) and then divide the result by a number (actually the number of pumps in the queryset, which I already have due to a Recordcount)

I can create the following for on date diff:

Expand|Select|Wrap|Line Numbers

 <cfset dtFrom = #CreateODBCDate(dateFormat(form.Pump_FromDate, 'dd/mm/yyyy'))# />

<cfset dtTo = #CreateODBCDate(dateFormat(form.Pump_ToDate, 'dd/mm/yyyy'))# />

<cfset dtDiff = (dtTo - dtFrom) />

<cfset ctDays = DateDiff ("d", dtFrom, dtTo)>

Can I:
- use this with the fields from my database that hold the two dates
- loop over the query to retrieve all the days as a total
- and ensure that it only counts working dates?

It's a big ask so any help would be appreciated!
thanks
Neil

ndeeley · #4 May 19th, 2009, 09:40 AM

Oh, and loop over the results where the second date is NULL!

ndeeley · #5 May 19th, 2009, 10:48 AM

Ah, another spanner...

I've created a query to loop through the recordset and return the dateDiff in ODBC Date format:

Expand|Select|Wrap|Line Numbers

 
    <table>

    <tr>

    <th>WONO</th>

    <th>Client</th>

    <th>PowerRating</th>

    <th>Logged Date</th>

    <th>Quote P Date</th>

    <th>No Of Days</th>

    </tr>
 
    <cfloop query="getPumpsDate">

    <cfset dtFrom = #CreateODBCDate(dateFormat(WORecdate, 'dd/mm/yyyy'))# />

    <cfset dtTo = #CreateODBCDate(dateFormat(QuotePDate, 'dd/mm/yyyy'))# />

    <cfset dtDiff = (dtTo - dtFrom) />

    <cfset ctDays = DateDiff ("d", dtFrom, dtTo)>

    <cfoutput>

    <tr>

    <td>#ClientWONO#</td>

    <td>#ClientFK#</td>

    <td>#PowerRating#</td>

    <td>#dateFormat(WORecdate, "dd/mm/yyyy")#</td>

    <td>#dateFormat(QuotePDate, "dd/mm/yyyy")#</td>

    <td>#ctDays#</td>

    </tr>

    </cfoutput>

    </cfloop>

    </table>

However the results are returning negative values in some instances - any idea why?

Logged Date Quote P Date No Of Days
06/04/2009 21/04/2009 -44
06/04/2009 22/04/2009 -43
16/04/2009 21/04/2009 5
24/04/2009 30/04/2009 6

I assume its seeing the month and day back to front, but don't know why...

acoder · #6 May 19th, 2009, 12:50 PM

You don't need to convert it to ODBC format. It's fine to use the date field from the database, WORecDate and QuotePDate in dateDiff. Line 14 is not required either.

ndeeley · #7 May 19th, 2009, 01:44 PM

Thanks acoder - it worked perfectly.

Now that i have created my loop and worked out all the days, do you know how I can add them all together? Plus is there any function that will calculate these in business days?

acoder · #8 May 19th, 2009, 02:14 PM

Keep a running count variable for the total and add ctDays on each round of the loop.

For business days, look at the DateAdd function with the "w" parameter.

ndeeley · #9 May 20th, 2009, 10:35 AM

Quote:

Originally Posted by acoder

Keep a running count variable for the total and add ctDays on each round of the loop.

For business days, look at the DateAdd function with the "w" parameter.

hi Acoder,

I've added the DateAdd function but it doesn't seem to work:

Expand|Select|Wrap|Line Numbers

 <cfset intDayTotal = 0>

        <cfloop query="KWBand1C">
 
        <cfset dtFrom = #DateAdd('w', -2, WORecdate)# />

        <cfset dtTo = #DateAdd('w', -2, QuotePDate)# />

        <cfset ctDays = DateDiff ("d", dtFrom, dtTo)>

        <cfset intDayTotal = intDayTotal + ctDays>

        <cfoutput>

Seemed a bit too easy to me - am I doing it wrong?
Thanks
Neil

acoder · #10 May 20th, 2009, 11:03 AM

Yes, that's a bit too simple. You need to use a while loop and date add one by one until it reaches the second date, e.g.

Expand|Select|Wrap|Line Numbers

 <cfscript>

function workingDaysBetween(date1,date2) {

    var numDays = 0;

    while (date1 LT date2) {

        date1 = dateAdd("w",1,date1);

        numDays = numDays + 1;

    }

    return numDays;

}

</cfscript>

(not tested)

Count the no of days between 2 dates

re: Count the no of days between 2 dates

re: Count the no of days between 2 dates

re: Count the no of days between 2 dates

re: Count the no of days between 2 dates

re: Count the no of days between 2 dates

re: Count the no of days between 2 dates

re: Count the no of days between 2 dates

re: Count the no of days between 2 dates

re: Count the no of days between 2 dates

/bytes/development

/bytes/it

/bytes/about