Home » SQL & PL/SQL » SQL & PL/SQL » display all dates of monday for given year
display all dates of monday for given year [message #246840] Fri, 22 June 2007 03:29 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

There is any way i can get the list of Monday dates for given year.

Like 2007


1-jan-07
8-jan-07
.
.
.
.

Please explain little bit your logic also..

--Yash
Re: display all dates of monday for given year [message #246842 is a reply to message #246840] Fri, 22 June 2007 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start with the first one and add 7 till the end of the year.
Here's a clue:
SQL> select rownum from dual connect by level <= 5;

    ROWNUM
----------
         1
         2
         3
         4
         5

5 rows selected.


Btw:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS.

Regards
Michel
Re: display all dates of monday for given year [message #246878 is a reply to message #246842] Fri, 22 June 2007 06:05 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Michel,


i tried to get the dates of Monday for one month.

select dates 
from(select rownum,TRUNC (SYSDATE, 'MM')+rownum-1 dates from dual connect by rownum <35) 
where to_char(dates,'dy')='mon' and dates < last_day(sysdate)


can you please suggest me some better way.

--Yash

[Updated on: Fri, 22 June 2007 06:27] by Moderator

Report message to a moderator

Re: display all dates of monday for given year [message #246883 is a reply to message #246878] Fri, 22 June 2007 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For the year, you just have to trunc on YEAR and loop on 366 days.
But it is better to only get the Monday starting from the first of the year and jumping per 7 days.
SQL> with lines as (select rownum-1 wk from dual connect by level<=53)
  2  select to_char(next_day(trunc(sysdate,'YEAR')-1,'MON')+7*wk,
  3                 'Day DD/MM/YYYY')
  4  from lines
  5  where next_day(trunc(sysdate,'YEAR')-1,'MON')+7*wk 
  6        < add_months(trunc(sysdate,'YEAR'),12)
  7  /
TO_CHAR(NEXT_DAY(TRU
--------------------
Monday    01/01/2007
Monday    08/01/2007
Monday    15/01/2007
...
Monday    17/12/2007
Monday    24/12/2007
Monday    31/12/2007

53 rows selected.

Regards
Michel
Re: display all dates of monday for given year [message #246886 is a reply to message #246883] Fri, 22 June 2007 06:43 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx michel
Previous Topic: Printing the column as rows in a table with the help of group by
Next Topic: Package
Goto Forum:
  


Current Time: Thu Dec 08 12:38:36 CST 2016

Total time taken to generate the page: 0.09716 seconds