Problem with CF7 dateDiff function – use countArbitraryDays instead

The CF7 livedocs dont mention anything about this so I added a comment. This is more on the subject.

There doesnt seem to be a way to DateDiff with weekdays (ie dont count weekends).

The docs say:
w: Weekdays (same as ww)
ww: Weeks

ie w and ww are synonymous, both mean weeks. That is true when you test it (see below).

The docs for DateAdd however say:
w: Weekday
ww: Week

ie w is days and ww is weeks! Inconsistant.

This code generates the output shown below:

<cfset date1 = CreateDateTime(2009,10,15,21,00,00)>

<cfoutput>date1=#date1#</cfoutput>

<table border=1>
<tr>
	<td>i</td>
	<td>Day() of<br>dateAdd('d',i,date1)</td>
	<td>Day() of<br>dateAdd('w',i,date1)</td>
	<td>dateDiff('d',date1,date2)</td>
	<td>dateDiff('w',date1,date2)</td>
</tr>

<cfloop index="i" from="0" to="10">
	<cfoutput>
		<cfset date2=dateAdd('d',i,date1)>
		<tr>
			<td>#i#</td>
			<td>#day(dateAdd('d',i,date1))#</td>
			<td>#day(dateAdd('w',i,date1))#</td>
			<td>#dateDiff('d',date1,date2)#</td>
			<td>#dateDiff('w',date1,date2)#</td>
		</tr>
	</cfoutput>
</cfloop>

</table>

date1={ts ‘2009-10-15 21:00:00’}

i Day() of
dateAdd(‘d’,i,date1)
Day() of
dateAdd(‘w’,i,date1)
dateDiff(‘d’,date1,date2) dateDiff(‘w’,date1,date2)
0 15 15 0 0
1 16 16 1 0
2 17 19 2 0
3 18 20 3 0
4 19 21 4 0
5 20 22 5 0
6 21 23 6 0
7 22 26 7 1
8 23 27 8 1
9 24 28 9 1
10 25 29 10 1

As you can see, the dateDiff(“w”…) does what the docs say but not what you might expect.

And dont forget that for DateDiff and DateAdd a “day” is a 24 hour period, so dateDiff(‘d’,{a date at 11pm},{the next day at 6am}) returns 0, not 1.

So, my solution (after testing various options including my own) was to use this http://www.cflib.org/udf/countArbitraryDays which seems to work correctly. It is also correct (IMJ) where the first date is (say) 11pm and the second date is at (say) 6am ie not whole 24 hour periods. countArbitraryDays counts these as whole days even if they are not 24 hours apart. That is what I wanted – if it is the next day, it is 1 day irresepective of whether a full 24 hours have elapsed.

Now, back to the job!

Advertisements