Oracle and Dates

[Glowball]

Hi all, I would like to put together a query that tells me the difference between two date fields. I have this query working, no problem. The issue is that I want to know the number of business days between the two dates, not the number of days flat-out. I want to skip Saturday and Sunday like they don't even exist.

Any ideas? Thanks!

 

[CFDude]

I did this with SQL server some time ago, so I am not exactly sure how Oracle would do it, but try restricting your record selection based on the day of the week.

e.g
Select whatever from whateverTable
Where
to_char(mydate,&quot;D&quot <> 1 AND
to_char(mydate,&quot;D&quot <> 7

-- 1 means sunday and 7 is Saturday. (I think -- I am not too sure.)

(SQL Server syntax would be different, but hopefully you get the idea)

 

[malleykr]

I don't know of a straight forward CF tag that will accomplish this for you, but the following code/logic will work:

<!---Set the value of the two dates--->
<cfset date1=&quot;11/01/2001&quot;>
<cfset date2=&quot;12/01/2001&quot;>

<!---Get the number of calendar days between the two dates--->
<cfset days=DateDiff(&quot;d&quot;,#date1#,#date2#)>

<!---Subtract out 2 weekend days for every full week--->
<cfset weeks=int(days/7)>
<cfset workdays=days - (2*weeks)>

<!---Figure out if any of the left over days were weekend days--->
<cfset dayofweek1=DatePart(&quot;d&quot;,#date1#)>
<cfset dayofweek2=DatePart(&quot;d&quot;,#date2#)>

<cfif dayofweek1 LT dayofweek2>
<cfif dayofweek1 is &quot;1&quot;>
<cfset workdays = workdays - 1>
</cfif>
<cfif dayofweek2 is &quot;7&quot;>
<cfset workdays = workdays - 1>
</cfif>
<cfelse> <!--- dayofweek1 LT dayofweek2--->
<cfset sub_days = 2>
<cfif dayofweek1 is &quot;7&quot;>
<cfset sub_days = sub_days - 1>
</cfif>
<cfif dayofweek2 is &quot;1&quot;>
<cfset sub_days = sub_days - 1>
</cfif>
<cfset workdays = workdays - sub_days>
</cfif>

<!---Display the results--->
<cfoutput>
The number of workdays between #date1# and #date2# is #workdays#.
</cfoutput>


Good luck!

 

[Glowball]

Thanks, I figured it out. This works great in Oracle only, skipping weekends. For example, the following code returns &quot;3&quot; -- whew!

<cfset requestDate = &quot;06-DEC-01&quot;>
<cfset sentDate = &quot;11-DEC-01&quot;>
<cfquery name=&quot;get&quot; datasource=&quot;myDSN&quot;>
SELECT GREATEST(TO_DATE('#sentDate#') - TO_DATE('#requestDate#') - 
2*(TO_CHAR(TO_DATE('#sentDate#'),'IW') - TO_CHAR(TO_DATE('#requestDate#'),'IW')) + 
DECODE(TO_CHAR(TO_DATE('#sentDate#'),'D'),7,-1,0) + 
DECODE(TO_CHAR(TO_DATE('#sentDate#'),'D'),1,-2,0) + 
DECODE(TO_CHAR(TO_DATE('#requestDate#'),'D'),1,1,0),0) &quot;part&quot; 
FROM dual 
</cfquery>

<cfoutput>Business Days: #get.part#</cfoutput>