Home » SQL & PL/SQL » SQL & PL/SQL » sql query to find fourdays before the current date excluding weekends (Oracle 10g)
sql query to find fourdays before the current date excluding weekends [message #416724] Tue, 04 August 2009 01:58 Go to next message
vkompell
Messages: 2
Registered: August 2009
Junior Member
I need a query to get the date which is four days before the current date. For business logic it should exclude Saturday and Sunday.
Re: sql query to find fourdays before the current date excluding weekends [message #416728 is a reply to message #416724] Tue, 04 August 2009 02:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry to disappoint you, but this site is not a query generator.
You will have to do some effort first, then we will help you. So show us what you tried already and where you got stuck.
Re: sql query to find fourdays before the current date excluding weekends [message #416729 is a reply to message #416724] Tue, 04 August 2009 02:13 Go to previous messageGo to next message
vkompell
Messages: 2
Registered: August 2009
Junior Member
Hi Frank,

no problem.. here is the query which is working fine i wanted to verufy this and also see if there is a better way to do

select
TRUNC(SYSDATE - TRUNC(6 - 2*floor(to_char(SYSDATE,'D')/6)
+ (floor(to_char(SYSDATE,'D')-2)/6)))
from dual
Re: sql query to find fourdays before the current date excluding weekends [message #416930 is a reply to message #416729] Wed, 05 August 2009 03:59 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Can be done in an easier way Smile
SELECT CASE
          WHEN TO_CHAR (SYSDATE, 'D') > 5
             THEN TRUNC (SYSDATE - 4)
          ELSE TRUNC (SYSDATE - 6)
       END
  FROM DUAL

[Updated on: Wed, 05 August 2009 03:59]

Report message to a moderator

Re: sql query to find fourdays before the current date excluding weekends [message #416986 is a reply to message #416930] Wed, 05 August 2009 09:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What about when you run it on Sunday?
Re: sql query to find fourdays before the current date excluding weekends [message #417084 is a reply to message #416986] Thu, 06 August 2009 00:18 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Sorry Shocked I missed that
SELECT CASE
          WHEN TO_CHAR (SYSDATE, 'D') = 1
             THEN TRUNC (SYSDATE - 5)
          WHEN TO_CHAR (SYSDATE, 'D') > 5
             THEN TRUNC (SYSDATE - 4)
          ELSE TRUNC (SYSDATE - 6)
       END
  FROM DUAL

Re: sql query to find fourdays before the current date excluding weekends [message #417168 is a reply to message #417084] Thu, 06 August 2009 06:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The thing that you need to watch with all of these solutions is the NLS settings.
For some NLS location, the first day of the week (what you get with the 'D' format mask) is Monday, and for others it is Sunday.
Previous Topic: Query a data inside a cursor (merged 4)
Next Topic: before update trigger
Goto Forum:
  


Current Time: Fri Dec 02 20:46:15 CST 2016

Total time taken to generate the page: 0.47114 seconds