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  |
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 #360179 is a reply to message #360170] |
Thu, 20 November 2008 00:11   |
 |
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>

Rajuvan.
|
|
|
|
|
Re: getting the closest monday date [message #360209 is a reply to message #360194] |
Thu, 20 November 2008 01:22   |
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   |
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 #361592 is a reply to message #360170] |
Thu, 27 November 2008 02:14   |
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 #361596 is a reply to message #361592] |
Thu, 27 November 2008 02:29   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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   |
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 #361616 is a reply to message #360170] |
Thu, 27 November 2008 03:26   |
 |
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 ?

Rajuvan.
|
|
|
|
Re: getting the closest monday date [message #361746 is a reply to message #360170] |
Thu, 27 November 2008 20:35   |
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 #361762 is a reply to message #361761] |
Thu, 27 November 2008 23:19  |
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..
|
|
|
Goto Forum:
Current Time: Thu Feb 06 23:56:30 CST 2025
|