469,270 Members | 1,164 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

Count the no of days between 2 dates

139 100+
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
  1. <cfset dtFrom = #CreateODBCDate(dateFormat(form.Pump_FromDate, 'dd/mm/yyyy'))# />
  2. <cfset dtTo = #CreateODBCDate(dateFormat(form.Pump_ToDate, 'dd/mm/yyyy'))# />
  3. <cfset dtDiff = (dtTo - dtFrom) />
But can't output the number of days between them as a whole number.
Can anyone help?

Thanks
Neil
May 18 '09 #1

✓ answered by acoder

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.

9 10424
acoder
16,027 Expert Mod 8TB
Use the dateDiff() function.
May 18 '09 #2
ndeeley
139 100+
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
  1. <cfset dtFrom = #CreateODBCDate(dateFormat(form.Pump_FromDate, 'dd/mm/yyyy'))# />
  2. <cfset dtTo = #CreateODBCDate(dateFormat(form.Pump_ToDate, 'dd/mm/yyyy'))# />
  3. <cfset dtDiff = (dtTo - dtFrom) />
  4. <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
May 19 '09 #3
ndeeley
139 100+
Oh, and loop over the results where the second date is NULL!
May 19 '09 #4
ndeeley
139 100+
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
  1.     <table>
  2.     <tr>
  3.     <th>WONO</th>
  4.     <th>Client</th>
  5.     <th>PowerRating</th>
  6.     <th>Logged Date</th>
  7.     <th>Quote P Date</th>
  8.     <th>No Of Days</th>
  9.     </tr>
  10.  
  11.     <cfloop query="getPumpsDate">
  12.     <cfset dtFrom = #CreateODBCDate(dateFormat(WORecdate, 'dd/mm/yyyy'))# />
  13.     <cfset dtTo = #CreateODBCDate(dateFormat(QuotePDate, 'dd/mm/yyyy'))# />
  14.     <cfset dtDiff = (dtTo - dtFrom) />
  15.     <cfset ctDays = DateDiff ("d", dtFrom, dtTo)>
  16.     <cfoutput>
  17.     <tr>
  18.     <td>#ClientWONO#</td>
  19.     <td>#ClientFK#</td>
  20.     <td>#PowerRating#</td>
  21.     <td>#dateFormat(WORecdate, "dd/mm/yyyy")#</td>
  22.     <td>#dateFormat(QuotePDate, "dd/mm/yyyy")#</td>
  23.     <td>#ctDays#</td>
  24.     </tr>
  25.     </cfoutput>
  26.     </cfloop>
  27.     </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...
May 19 '09 #5
acoder
16,027 Expert Mod 8TB
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.
May 19 '09 #6
ndeeley
139 100+
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?
May 19 '09 #7
acoder
16,027 Expert Mod 8TB
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.
May 19 '09 #8
ndeeley
139 100+
@acoder
hi Acoder,

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

Expand|Select|Wrap|Line Numbers
  1. <cfset intDayTotal = 0>
  2.         <cfloop query="KWBand1C">
  3.  
  4.         <cfset dtFrom = #DateAdd('w', -2, WORecdate)# />
  5.         <cfset dtTo = #DateAdd('w', -2, QuotePDate)# />
  6.         <cfset ctDays = DateDiff ("d", dtFrom, dtTo)>
  7.         <cfset intDayTotal = intDayTotal + ctDays>
  8.         <cfoutput>
Seemed a bit too easy to me - am I doing it wrong?
Thanks
Neil
May 20 '09 #9
acoder
16,027 Expert Mod 8TB
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
  1. <cfscript>
  2. function workingDaysBetween(date1,date2) {
  3.     var numDays = 0;
  4.     while (date1 LT date2) {
  5.         date1 = dateAdd("w",1,date1);
  6.         numDays = numDays + 1;
  7.     }
  8.     return numDays;
  9. }
  10. </cfscript>
(not tested)
May 20 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Bambero | last post: by
2 posts views Thread by jpr | last post: by
2 posts views Thread by DeanO | last post: by
2 posts views Thread by ghjk | last post: by
7 posts views Thread by Mike | last post: by
9 posts views Thread by sha2484 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.