Home » SQL & PL/SQL » SQL & PL/SQL » getting the closest monday date (oracle 10.2.0.3)
getting the closest monday date [message #360170] Wed, 19 November 2008 23:39 Go to next message
saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad
Junior Member
Hi all,

i have a requirement that i need to get the closest monday date from the date when i am going to execute my script.

Please help me with your ideas.

thanks
Sai Kumar
Re: getting the closest monday date [message #360175 is a reply to message #360170] Thu, 20 November 2008 00:05 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select trunc(sysdate, 'IW') from dual;

TRUNC(SYS
---------
17-NOV-08
Re: getting the closest monday date [message #360177 is a reply to message #360170] Thu, 20 November 2008 00:09 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi,

Try searching for answers in this forum first before posting a new one. Anyway, search topics on SYSDATE and NEXT_DAY function. Hope that helps. Smile

Regards,
Wilbert
Re: getting the closest monday date [message #360179 is a reply to message #360170] Thu, 20 November 2008 00:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One way would be ,

SQL> SELECT TRUNC(SYSDATE) ,
  2         Next_day(TRUNC(SYSDATE),'MON') day1,
  3         ABS(TRUNC(SYSDATE) - Next_day(TRUNC(SYSDATE),'MON')) offset
  4  FROM DUAL UNION
  5  SELECT TRUNC(SYSDATE) ,
  6         Next_day(TRUNC(SYSDATE)-7,'MON') day1,
  7         ABS(TRUNC(SYSDATE) - Next_day(TRUNC(SYSDATE)-7,'MON'))
  8  FROM DUAL;

TRUNC(SYS DAY1          OFFSET
--------- --------- ----------
20-NOV-08 17-NOV-08          3
20-NOV-08 24-NOV-08          4

SQL> WITH REC AS (
  2  SELECT TRUNC(SYSDATE) ,
  3         NEXT_DAY(TRUNC(SYSDATE),'MON') DAY1,
  4         ABS(TRUNC(SYSDATE) - NEXT_DAY(TRUNC(SYSDATE),'MON')) OFFSET
  5  FROM DUAL UNION
  6  SELECT TRUNC(SYSDATE) ,
  7         NEXT_DAY(TRUNC(SYSDATE)-7,'MON') DAY1,
  8         ABS(TRUNC(SYSDATE) - NEXT_DAY(TRUNC(SYSDATE)-7,'MON'))
  9  FROM DUAL)
 10  SELECT *  FROM  REC WHERE OFFSET = ( SELECT MIN (OFFSET) FROM REC) ;

TRUNC(SYS DAY1          OFFSET
--------- --------- ----------
20-NOV-08 17-NOV-08          3

SQL>



Smile
Rajuvan.
Re: getting the closest monday date [message #360192 is a reply to message #360170] Thu, 20 November 2008 00:49 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I may have mis-interpreted the requirement.

If you want the absolute closest Monday and not just the closest Monday of the current week, in addition to Rajuvan's solution, another option is:

SQL> with t as(
  2     select trunc(sysdate, 'IW') d1,
  3            trunc(sysdate+7, 'IW')d2
  4     from dual)
  5  select case when (sysdate - (d1+1)) > (d2 - sysdate)
  6     then d2
  7     else d1 end "Closest Monday"
  8  from t;

Closest Monday
---------------
17-NOV-08

Re: getting the closest monday date [message #360194 is a reply to message #360170] Thu, 20 November 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as ( select trunc(sysdate)+level-4 mydate from dual connect by level <= 8 )
  2  select mydate,
  3         next_day(mydate,'MON')
  4         - case when next_day(mydate,'MON')-mydate > 3 then 7 else 0 end
  5           closest_monday
  6  from data
  7  /
MYDATE      CLOSEST_MON
----------- -----------
17-NOV-2008 17-NOV-2008
18-NOV-2008 17-NOV-2008
19-NOV-2008 17-NOV-2008
20-NOV-2008 17-NOV-2008
21-NOV-2008 24-NOV-2008
22-NOV-2008 24-NOV-2008
23-NOV-2008 24-NOV-2008
24-NOV-2008 24-NOV-2008

8 rows selected.

Regards
Michel
Re: getting the closest monday date [message #360209 is a reply to message #360194] Thu, 20 November 2008 01:22 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Never knew there would be several variations for this. Nice, will look up on all of your suggestions.

Anyway what does this mean exactly?

IW - Same day of the week as the first day of the ISO year

Thanks,
Wilbert
Re: getting the closest monday date [message #360248 is a reply to message #360209] Thu, 20 November 2008 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
An easier to understand implementation of Michels solution:
select to_char(dte,'Dy')
      ,dte
      ,trunc(dte+3,'IW') nearest_mon
      ,abs(dte-trunc(dte+3,'IW')) days_away
from (select trunc(sysdate)+level dte 
      from   dual connect by level <= 30);


The algorithm is: Add 3 days to the date you're looking at, and then move that date back to the previous monday.
If the original date was more than 3 days after a Monday, then this will find a Monday closer to the original date.
If the original date was 3 days or less after a Monday, then this will be the same mMonday you'd have got by truncing the original date.
Re: getting the closest monday date [message #361588 is a reply to message #360170] Thu, 27 November 2008 01:54 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

Try this..

DECLARE
name_day VARCHAR(100);
closest_monday VARCHAR2(100);
BEGIN
FOR i IN 1..7 LOOP
SELECT TO_CHAR(:the_date - i, 'Day'), TO_CHAR(:the_date - i, 'MM-DD-RRRR')
INTO name_day, closest_monday
FROM dual;
IF name_day = 'Monday ' THEN
DBMS_OUTPUT.PUT_LINE(closest_monday);
END IF;
END LOOP;
END;

Smile
Re: getting the closest monday date [message #361592 is a reply to message #360170] Thu, 27 November 2008 02:14 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

or this,

SELECT :the_date, DECODE(TO_CHAR(:the_date - 1,'Day'),
'Monday ', TO_CHAR(:the_date - 1, 'MM-DD-RRRR'), NULL)||
DECODE(TO_CHAR(:the_date - 2,'Day'),
'Monday ', TO_CHAR(:the_date - 2, 'MM-DD-RRRR'), NULL)||
DECODE(TO_CHAR(:the_date - 3,'Day'),
'Monday ', TO_CHAR(:the_date - 3, 'MM-DD-RRRR'), NULL)||
DECODE(TO_CHAR(:the_date - 4,'Day'),
'Monday ', TO_CHAR(:the_date - 4, 'MM-DD-RRRR'), NULL)||
DECODE(TO_CHAR(:the_date - 5,'Day'),
'Monday ', TO_CHAR(:the_date - 5, 'MM-DD-RRRR'), NULL)||
DECODE(TO_CHAR(:the_date - 6,'Day'),
'Monday ', TO_CHAR(:the_date - 6, 'MM-DD-RRRR'), NULL)||
DECODE(TO_CHAR(:the_date - 7,'Day'),
'Monday ', TO_CHAR(:the_date - 7, 'MM-DD-RRRR'), NULL) closest_monday
FROM dual;
Re: getting the closest monday date [message #361595 is a reply to message #361592] Thu, 27 November 2008 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ice_cream_hehe

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: getting the closest monday date [message #361596 is a reply to message #361592] Thu, 27 November 2008 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How your query is giving the closest Monday?
SQL> SELECT sysdate, DECODE(TO_CHAR(sysdate - 1,'Day'), 
  2  'Monday ', TO_CHAR(sysdate - 1, 'MM-DD-RRRR'), NULL)||
  3  DECODE(TO_CHAR(sysdate - 2,'Day'), 
  4  'Monday ', TO_CHAR(sysdate - 2, 'MM-DD-RRRR'), NULL)||
  5  DECODE(TO_CHAR(sysdate - 3,'Day'), 
  6  'Monday ', TO_CHAR(sysdate - 3, 'MM-DD-RRRR'), NULL)||
  7  DECODE(TO_CHAR(sysdate - 4,'Day'), 
  8  'Monday ', TO_CHAR(sysdate - 4, 'MM-DD-RRRR'), NULL)||
  9  DECODE(TO_CHAR(sysdate - 5,'Day'), 
 10  'Monday ', TO_CHAR(sysdate - 5, 'MM-DD-RRRR'), NULL)||
 11  DECODE(TO_CHAR(sysdate - 6,'Day'), 
 12  'Monday ', TO_CHAR(sysdate - 6, 'MM-DD-RRRR'), NULL)||
 13  DECODE(TO_CHAR(sysdate - 7,'Day'), 
 14  'Monday ', TO_CHAR(sysdate - 7, 'MM-DD-RRRR'), NULL) closest_monday
 15  FROM dual; 
SYSDATE     CLOSEST_MONDAY
----------- ----------------------------------------------------------------------
27-NOV-2008

1 row selected.

Regards
Michel
Re: getting the closest monday date [message #361603 is a reply to message #360170] Thu, 27 November 2008 03:09 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

Michel,
The length of the 'Monday ' should be 9 to get the closest Monday..

SQL> SELECT :the_date, DECODE(RTRIM(TO_CHAR(:the_date - 1,'Day'), 1 ' '),'Monday', TO_CHAR(:the_date - 1, 'MM-DD-RRRR'), NULL)||
2 DECODE(RTRIM(TO_CHAR(:the_date - 2,'Day'), ' '), 'Monday', 3 TO_CHAR(:the_date - 2, 'MM-DD-RRRR'), NULL)||
4 DECODE(RTRIM(TO_CHAR(:the_date - 3,'Day'), ' '), 'Monday', 5 TO_CHAR(:the_date - 3, 'MM-DD-RRRR'), NULL)||
6 DECODE(RTRIM(TO_CHAR(:the_date - 4,'Day'), ' '), 'Monday', 7 TO_CHAR(:the_date - 4, 'MM-DD-RRRR'), NULL)||
8 DECODE(RTRIM(TO_CHAR(:the_date - 5,'Day'), ' '), 'Monday', 9 TO_CHAR(:the_date - 5, 'MM-DD-RRRR'), NULL)||
10 DECODE(RTRIM(TO_CHAR(:the_date - 6,'Day'), ' '), 'Monday', 11 TO_CHAR(:the_date - 6, 'MM-DD-RRRR'), NULL)||
12 DECODE(RTRIM(TO_CHAR(:the_date - 7,'Day'), ' '), 'Monday', 13 TO_CHAR(:the_date - 7, 'MM-DD-RRRR'), NULL) closest_monday
14 FROM dual;

It will work this time..

Regards,

ice_cream_hehe


Re: getting the closest monday date [message #361605 is a reply to message #360170] Thu, 27 November 2008 03:12 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

SELECT :the_date, 2 DECODE(RTRIM(TO_CHAR(:the_date - 1,'Day'), ' 3 '),'Monday', TO_CHAR(:the_date - 1, 'MM-DD-RRRR'), 4 NULL)||
DECODE(RTRIM(TO_CHAR(:the_date - 2,'Day'), ' '), 'Monday', TO_CHAR(:the_date - 2, 'MM-DD-RRRR'), NULL)||
DECODE(RTRIM(TO_CHAR(:the_date - 3,'Day'), ' '), 'Monday', TO_CHAR(:the_date - 3, 'MM-DD-RRRR'), NULL)||
DECODE(RTRIM(TO_CHAR(:the_date - 4,'Day'), ' '), 'Monday', TO_CHAR(:the_date - 4, 'MM-DD-RRRR'), NULL)||
DECODE(RTRIM(TO_CHAR(:the_date - 5,'Day'), ' '), 'Monday', TO_CHAR(:the_date - 5, 'MM-DD-RRRR'), NULL)||
DECODE(RTRIM(TO_CHAR(:the_date - 6,'Day'), ' '), 'Monday', TO_CHAR(:the_date - 6, 'MM-DD-RRRR'), NULL)||
DECODE(RTRIM(TO_CHAR(:the_date - 7,'Day'), ' '), 'Monday', TO_CHAR(:the_date - 7, 'MM-DD-RRRR'), NULL) closest_monday
FROM dual;

i think i'd made it in the wrong format..
try the select statement above anyway..
Re: getting the closest monday date [message #361615 is a reply to message #361603] Thu, 27 November 2008 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The length of the 'Monday ' should be 9 to get the closest Monday..

This is one of the reasons you post MUST be formatted and it is still not.

In addition, if you are on Friday, the closest Monday is the next one NOT the previous one.

Regards
Michel
Re: getting the closest monday date [message #361616 is a reply to message #360170] Thu, 27 November 2008 03:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Wrong !!!


SQL> SELECT SYSDATE , TO_CHAR(SYSDATE,'DAY') DAY FROM DUAL;

SYSDATE   DAY
--------- ---------
27-NOV-08 THURSDAY

SQL> SELECT SYSDATE+2 , TO_CHAR(SYSDATE+2,'DAY') DAY FROM DUAL;

SYSDATE+2 DAY
--------- ---------
29-NOV-08 SATURDAY

SQL> SELECT SYSDATE,
  2  DECODE(RTRIM(TO_CHAR(SYSDATE - 1,'Day'), ' '),'Monday',
  3     TO_CHAR(SYSDATE - 1, 'MM-DD-RRRR'), NULL)||
  4  DECODE(RTRIM(TO_CHAR(SYSDATE - 2,'Day'), ' '), 'Monday',
  5     TO_CHAR(SYSDATE - 2, 'MM-DD-RRRR'), NULL)||
  6  DECODE(RTRIM(TO_CHAR(SYSDATE - 3,'Day'), ' '), 'Monday',
  7     TO_CHAR(SYSDATE - 3, 'MM-DD-RRRR'), NULL)||
  8  DECODE(RTRIM(TO_CHAR(SYSDATE - 4,'Day'), ' '), 'Monday',
  9     TO_CHAR(SYSDATE - 4, 'MM-DD-RRRR'), NULL)||
 10  DECODE(RTRIM(TO_CHAR(SYSDATE - 5,'Day'), ' '), 'Monday',
 11     TO_CHAR(SYSDATE - 5, 'MM-DD-RRRR'), NULL)||
 12  DECODE(RTRIM(TO_CHAR(SYSDATE - 6,'Day'), ' '), 'Monday',
 13     TO_CHAR(SYSDATE - 6, 'MM-DD-RRRR'), NULL)||
 14  DECODE(RTRIM(TO_CHAR(SYSDATE - 7,'Day'), ' '), 'Monday',
 15     TO_CHAR(SYSDATE - 7, 'MM-DD-RRRR'), NULL) closest_monday
 16  FROM dual;

SYSDATE   CLOSEST_MONDAY
--------- ---------------------------------------------------------------------

27-NOV-08 11-24-2008

SQL> SELECT SYSDATE+2,
  2  DECODE(RTRIM(TO_CHAR(SYSDATE+2 - 1,'Day'), ' '),'Monday',
  3     TO_CHAR(SYSDATE+2 - 1, 'MM-DD-RRRR'), NULL)||
  4  DECODE(RTRIM(TO_CHAR(SYSDATE+2 - 2,'Day'), ' '), 'Monday',
  5     TO_CHAR(SYSDATE+2 - 2, 'MM-DD-RRRR'), NULL)||
  6  DECODE(RTRIM(TO_CHAR(SYSDATE+2 - 3,'Day'), ' '), 'Monday',
  7     TO_CHAR(SYSDATE+2 - 3, 'MM-DD-RRRR'), NULL)||
  8  DECODE(RTRIM(TO_CHAR(SYSDATE+2 - 4,'Day'), ' '), 'Monday',
  9     TO_CHAR(SYSDATE+2 - 4, 'MM-DD-RRRR'), NULL)||
 10  DECODE(RTRIM(TO_CHAR(SYSDATE+2 - 5,'Day'), ' '), 'Monday',
 11     TO_CHAR(SYSDATE+2 - 5, 'MM-DD-RRRR'), NULL)||
 12  DECODE(RTRIM(TO_CHAR(SYSDATE+2 - 6,'Day'), ' '), 'Monday',
 13     TO_CHAR(SYSDATE+2 - 6, 'MM-DD-RRRR'), NULL)||
 14  DECODE(RTRIM(TO_CHAR(SYSDATE+2 - 7,'Day'), ' '), 'Monday',
 15     TO_CHAR(SYSDATE+2 - 7, 'MM-DD-RRRR'), NULL) closest_monday
 16  FROM dual;

SYSDATE+2 CLOSEST_MONDAY
--------- ---------------------------------------------------------------------

29-NOV-08 11-24-2008

SQL>


Which is Closest Monday to 29-NOV-08 ? 24-NOV-2008 Or 01-DEC-2008 ?

Smile
Rajuvan.


Re: getting the closest monday date [message #361628 is a reply to message #360170] Thu, 27 November 2008 03:42 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

okay..it's a mistake..i'll post my other solution tomorrow..
thanks michel and rajuvan Smile

ice_cream_hehe
Re: getting the closest monday date [message #361746 is a reply to message #360170] Thu, 27 November 2008 20:35 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

hey, kindly check this..

SELECT :the_date Entered_date,
       TO_CHAR(:the_date,'Day') Day_of_the_entered_date,
	   DECODE((SELECT RTRIM(TO_CHAR(:the_date,'D'), ' ') FROM dual),
                 '2', TO_CHAR(:the_date, 'MM-DD-RRRR'),
				 '3', TO_CHAR(:the_date - 1, 'MM-DD-RRRR'),
				 '4',TO_CHAR(:the_date - 2, 'MM-DD-RRRR'),
				 '5', TO_CHAR(:the_date - 3, 'MM-DD-RRRR'),
				 '6', TO_CHAR(NEXT_DAY(:the_date,2),'MM-DD-RRRR'),
				 '7', TO_CHAR(NEXT_DAY(:the_date,2),'MM-DD-RRRR'),
				 '1', TO_CHAR(NEXT_DAY(:the_date,2),'MM-DD-RRRR')) Closest_Monday
	FROM dual;

Re: getting the closest monday date [message #361761 is a reply to message #361746] Thu, 27 November 2008 23:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There have been several answers on this above, so if you want to post a new one, please check and PROVE it works.

Regards
Michel
Re: getting the closest monday date [message #361762 is a reply to message #361761] Thu, 27 November 2008 23:19 Go to previous message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

Michel Cadot wrote on Thu, 27 November 2008 23:13
There have been several answers on this above, so if you want to post a new one, please check and PROVE it works.

Regards
Michel



okay..

thanks..
Previous Topic: DBMS_JOB
Next Topic: How Connect To Database Locally On Server.
Goto Forum:
  


Current Time: Fri Dec 09 19:46:17 CST 2016

Total time taken to generate the page: 0.15593 seconds