I have an Oracle table that has 2 Date fields..i.e. the data type -
DATE

Field 1: START_DATE sample value "2008-04-04 12:00:00";
Field 2: END_DATE sample value "2008-04-07 05:46:07";

If I subtract one date from another, I can get the time difference.

Round((END_DATE - START_DATE), 4) AS TOTAL_DAYS

The above results in 3.7404

Between 2008-04-04 and 2008-04-07, there is Saturday and Sunday which
are 2008-04-05 and 2008-04-06.
I would like them to be excluded.

Hence, the resultant that I'm looking for should be 1.7404 -- not
3.7404

Is there a simple way to get the desired solution?

Thanks,
Deepak

On Apr 30, 9:49*am, deepakp <deepak10...@hotmail.com> wrote:
> I have an Oracle table that has 2 Date fields..i.e. the data type -
> DATE
>
> Field 1: *START_DATE sample value *"2008-04-04 12:00:00";
> Field 2: *END_DATE *sample value *"2008-04-07 05:46:07";
>
> If I subtract one date from another, I can get the time difference.
>
> Round((END_DATE - START_DATE), 4) AS TOTAL_DAYS
>
> The above results in 3.7404
>
> Between 2008-04-04 and 2008-04-07, there is Saturday and Sunday which
> are 2008-04-05 and 2008-04-06.
> I would like them to be excluded.
>
> Hence, the resultant that I'm looking for should be 1.7404 *-- not
> 3.7404
>
> Is there a simple way to get the desired solution?
>
> Thanks,
> Deepak

Have you tried a Google search of the Usenet archives, or even a
regular Google search?
http://groups.google.com/group/comp....8545a4f3698ee0
http://groups.google.com/group/comp....d8621182ae7d1b
http://www.google.com/search?hl=en&q... usiness+days

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Apr 30, 9:29 am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On Apr 30, 9:49 am, deepakp <deepak10...@hotmail.com> wrote:
>
>
>
> > I have an Oracle table that has 2 Date fields..i.e. the data type -
> > DATE
>
> > Field 1: START_DATE sample value "2008-04-04 12:00:00";
> > Field 2: END_DATE sample value "2008-04-07 05:46:07";
>
> > If I subtract one date from another, I can get the time difference.
>
> > Round((END_DATE - START_DATE), 4) AS TOTAL_DAYS
>
> > The above results in 3.7404
>
> > Between 2008-04-04 and 2008-04-07, there is Saturday and Sunday which
> > are 2008-04-05 and 2008-04-06.
> > I would like them to be excluded.
>
> > Hence, the resultant that I'm looking for should be 1.7404 -- not
> > 3.7404
>
> > Is there a simple way to get the desired solution?
>
> > Thanks,
> > Deepak
>
> Have you tried a Google search of the Usenet archives, or even a
> regular Google search?http://groups.google.com/group/comp....le.com+weekend...
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Thank you for your response. I did search online and could not find
any that showed time difference between. The examples I found were
similar to the ones you pasted..which show count of days between 2
dates. That is not what I'm looking for. Instead, I'm looking for time
difference excluding weekends. The closest match I found in online
search was http://searchoracle.techtarget.com/e...294550,00.html

Kind Regards,
Deepak

On Apr 30, 12:45*pm, deepakp <deepak10...@hotmail.com> wrote:
> On Apr 30, 9:29 am, Charles Hooper <hooperc2...@yahoo.com> wrote:
>
>
>
>
>
> > On Apr 30, 9:49 am, deepakp <deepak10...@hotmail.com> wrote:
>
> > > I have an Oracle table that has 2 Date fields..i.e. the data type -
> > > DATE
>
> > > Field 1: *START_DATE sample value *"2008-04-04 12:00:00";
> > > Field 2: *END_DATE *sample value *"2008-04-07 05:46:07";
>
> > > If I subtract one date from another, I can get the time difference.
>
> > > Round((END_DATE - START_DATE), 4) AS TOTAL_DAYS
>
> > > The above results in 3.7404
>
> > > Between 2008-04-04 and 2008-04-07, there is Saturday and Sunday which
> > > are 2008-04-05 and 2008-04-06.
> > > I would like them to be excluded.
>
> > > Hence, the resultant that I'm looking for should be 1.7404 *-- not
> > > 3.7404
>
> > > Is there a simple way to get the desired solution?
>
> > > Thanks,
> > > Deepak
>
> > Have you tried a Google search of the Usenet archives, or even a
> > regular Google search?http://groups.google.com/group/comp....sc/browse_thre......
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Thank you for your response. I did search online and could not find
> any that showed time difference between. The examples I found were
> similar to the ones you pasted..which show count of days between 2
> dates. That is not what I'm looking for. Instead, I'm looking for time
> difference excluding weekends. *The closest match I found in online
> search washttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,28962...

Assuming that date2 is the later date and that neither date falls on a
Saturday or Sunday, this should do it:

date2 - date1 - 2*(trunc(next_day(date2-1,'FRI')) -
trunc(next_day(date1-1,'FRI')))/7

On Apr 30, 2:21 pm, Ken Denny <k...@kendenny.com> wrote:
<snip>
> Assuming that date2 is the later date and that neither date falls on a
> Saturday or Sunday, this should do it:
>
> date2 - date1 - 2*(trunc(next_day(date2-1,'FRI')) -
> trunc(next_day(date1-1,'FRI')))/7
</snip>

Ken,

Thank you so much..this works perfectly.

Deepak