 | |
How to calculate time difference excluding weekends (saturday andsunday)
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
How to calculate time difference excluding weekends (saturday andsunday) -
04-30-2008
, 08:49 AM
#2
| |||
| |||
|
|
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 |
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.
#3
| |||
| |||
|
|
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 |
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.
#4
| |||
| |||
|
|
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 |
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.
#5
| |||
| |||
|
|
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 |
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.
#6
| |||
| |||
|
|
On Apr 30, 9:49 am, deepakp <deepak10... (AT) hotmail (DOT) 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. |
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
#7
| |||
| |||
|
|
On Apr 30, 9:49 am, deepakp <deepak10... (AT) hotmail (DOT) 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. |
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
#8
| |||
| |||
|
|
On Apr 30, 9:49 am, deepakp <deepak10... (AT) hotmail (DOT) 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. |
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
#9
| |||
| |||
|
|
On Apr 30, 9:49 am, deepakp <deepak10... (AT) hotmail (DOT) 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. |
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
#10
| |||
| |||
|
|
On Apr 30, 9:29 am, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote: On Apr 30, 9:49 am, deepakp <deepak10... (AT) hotmail (DOT) 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... |
Saturday or Sunday, this should do it:
date2 - date1 - 2*(trunc(next_day(date2-1,'FRI')) -
trunc(next_day(date1-1,'FRI')))/7
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.