Home » SQL & PL/SQL » SQL & PL/SQL » Display calendar between two dates (oracle 10g)
Display calendar between two dates [message #357077] Mon, 03 November 2008 15:40 Go to next message
oradev8
Messages: 8
Registered: April 2007
Junior Member
Hi ,

I need to display calendar for a month as shown below. It should always start on sunday and end on saturday. Input for this should be month and year.

NOV 2008:

SUN MON TUE WED THU FRI SAT
---- ------- ------- ------- ------- ------- ----------
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 1 2 3 4 5 6

I got the following code from other website..but it displays only dates from 1-30 for a month.
select
2 max(decode(dow,1,d,null)) Sun,
3 max(decode(dow,2,d,null)) Mon,
4 max(decode(dow,3,d,null)) Tue,
5 max(decode(dow,4,d,null)) Wed,
6 max(decode(dow,5,d,null)) Thu,
7 max(decode(dow,6,d,null)) Fri,
8 max(decode(dow,7,d,null)) Sat
9 from
10 ( select rownum d,
11 rownum-2+to_number(
12 to_char(trunc(
13 to_date('&mdate'),'MM'),'D')) p,
14 to_char(trunc(to_date('&mdate'),'MM')
15 -1+rownum,'D') dow
16 from all_objects
17 where rownum <= to_number(to_char(
18 last_day(to_date('&mdate')),'DD')))
19 group by trunc(p/7)
20 /

output:
SUN MON TUE WED THU FRI SAT
---- ------- ------- ------- ------- ------- ----------
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30


Can anyone please help me with this. Thanks in advance.
Re: Display calendar between two dates [message #357120 is a reply to message #357077] Tue, 04 November 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
http://www.orafaq.com/forum/t/94953/102589/

Regards
Michel
Re: Display calendar between two dates [message #357254 is a reply to message #357077] Tue, 04 November 2008 07:58 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
You want your output to have no Fridays or Saturdays and multiple days on Monday, Tuesday and Wednesday? That's what it "looks" like to me Wink

Also, TO_DATE takes on two parameters. You are using it incorrectly.
Re: Display calendar between two dates [message #357301 is a reply to message #357077] Tue, 04 November 2008 14:45 Go to previous messageGo to next message
oradev8
Messages: 8
Registered: April 2007
Junior Member
Thanks for a reply Michel and joy_divison.

I got the code for displaying all the days in a month. But I need to display dates from previous month and next month id the month doesnt start on sunday and end on saturday.

The code in the link gives the follwing output:
Sun Mon Tue Wed Thu Fri Sat
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30

But the desired output is(I need to display dates in red as well):
Sun Mon Tue Wed Thu Fri Sat
28 29 30 31 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 1

I am not able to align the calendar correctly..so please check the atatchment
  • Attachment: cal.txt
    (Size: 0.46KB, Downloaded 670 times)

[Updated on: Tue, 04 November 2008 14:47]

Report message to a moderator

Re: Display calendar between two dates [message #357305 is a reply to message #357301] Tue, 04 November 2008 15:31 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
oradev8 wrote on Tue, 04 November 2008 15:45
T
I am not able to align the calendar correctly..so please check the atatchment


Please read the "sticky" for the forums which gives instructions on how to use CODE tags.
Re: Display calendar between two dates [message #357328 is a reply to message #357301] Tue, 04 November 2008 22:54 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have several solutions in the mentioned links, now work a little bit, adapt them and come back with the solution you'll find.

Regards
Michel
Previous Topic: How to run .sql script from pl/sql
Next Topic: Interesting SQL problem
Goto Forum:
  


Current Time: Mon Nov 11 00:22:05 CST 2024