OraFAQ Forum: SQL & PL/SQL » moths and dates

Home » SQL & PL/SQL » SQL & PL/SQL » moths and dates

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

moths and dates [message #228830]

Wed, 04 April 2007 06:12

pavuluri
Messages: 247
Registered: January 2007

Senior Member

my reqirement is

month date
jan 1,2,3,............................31
feb 1,2,..............................28
march 1,2..............................

plase help me.

Thanks
srinivas

Report message to a moderator

Re: moths and dates [message #228842 is a reply to message #228830]

Wed, 04 April 2007 06:51

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What do you want?
Do you want a query that returns:

month date
jan 1,2,3,............................31
feb 1,2,..............................28
march 1,2..............................

Use:

select 'month date
jan 1,2,3,............................31
feb 1,2,..............................28
march 1,2.............................. '
from dual;

Regards
Michel

Report message to a moderator

Re: moths and dates [message #228843 is a reply to message #228842]

Wed, 04 April 2007 06:52

pavuluri
Messages: 247
Registered: January 2007

Senior Member

ok great
thanks
srinivas

Report message to a moderator

Re: moths and dates [message #228854 is a reply to message #228843]

Wed, 04 April 2007 07:12

Frank
Messages: 7901
Registered: March 2000

Senior Member

Do you even read the replies you get?

Report message to a moderator

Re: moths and dates [message #228858 is a reply to message #228854]

Wed, 04 April 2007 07:30

pavuluri
Messages: 247
Registered: January 2007

Senior Member

sorry
thanks
srinivas

Report message to a moderator

Re: moths and dates [message #228867 is a reply to message #228858]

Wed, 04 April 2007 08:04

pavuluri
Messages: 247
Registered: January 2007

Senior Member

hi
my reqirement is

i want display this formate

month dates
.................
jan 1,2,3 ........................31
feb 1,2,3..........................28
march 1,2,3...........................31
april 1,2,3,4
.........................................................

april 4 means is today.

Report message to a moderator

Re: moths and dates [message #228869 is a reply to message #228867]

Wed, 04 April 2007 08:07

pavuluri
Messages: 247
Registered: January 2007

Senior Member

month is column and dates is another column
thanks
srinivas

Report message to a moderator

Re: moths and dates [message #228873 is a reply to message #228869]

Wed, 04 April 2007 08:10

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Then why don't you show it as such.
Try again, and use CODE tags.
You're so lazy.

Report message to a moderator

Re: moths and dates [message #228876 is a reply to message #228869]

Wed, 04 April 2007 08:14

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How many dots do you want to display?

Regards
Michel

Report message to a moderator

Re: moths and dates [message #228877 is a reply to message #228873]

Wed, 04 April 2007 08:17

pavuluri
Messages: 247
Registered: January 2007

Senior Member

select to_char(dates,'Month'), max(substr(sys_connect_by_path(day,','),2)) days
from
(
select t.dates ,days.day
from
(
select trunc(sysdate) dates , to_char(sysdate,'DD') days from dual
)t,
(
select rownum day from dict where rownum<=31) days
where days.day<=t.days
)
start with day=1
connect by prior dates=dates and day =prior day +1
group by dates

iam getting

April 1,2,3,4

I want to previous months dates also

plz modified this query

thnaks
srinivas

Report message to a moderator

Re: moths and dates [message #228881 is a reply to message #228877]

Wed, 04 April 2007 08:20

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Still no CODE tags.

Report message to a moderator

Re: moths and dates [message #228882 is a reply to message #228881]

Wed, 04 April 2007 08:22

pavuluri
Messages: 247
Registered: January 2007

Senior Member

sorry joy_division

really i dont know CODE tags

Report message to a moderator

Re: moths and dates [message #228886 is a reply to message #228882]

Wed, 04 April 2007 08:29

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

It is very simple to use...

take a look

select *
from table_name
order by column_name

without CODE is hard to read.
but with CODE tags it is easy
you have to just add at beginning or end of each query like
at beginning

"[code]"

at end

"[/code]"

select * 
from table_name
order by column_name

Go below link it will explain better.
http://www.orafaq.com/forum/?t=help_index&section=readingposting&S=93410#code

Regards
Taj

[Updated on: Wed, 04 April 2007 08:32]

Report message to a moderator

Re: moths and dates [message #228892 is a reply to message #228886]

Wed, 04 April 2007 08:36

pavuluri
Messages: 247
Registered: January 2007

Senior Member

0k
thanks

"select to_char(dates,'Month'), max(substr(sys_connect_by_path(day,','),2)) days
from
(
select t.dates ,days.day
from
(
select trunc(sysdate) dates , to_char(sysdate,'DD') days from dual
)t,
(
select rownum day from dict where rownum<=31) days
where days.day<=t.days
)
start with day=1
connect by prior dates=dates and day =prior day +1
group by dates"

iam geting only

"april 1,2,3,4"

i want to

"jan" "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,31"

"feb" "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"

"march" "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,31"

"april" 1,2,3,4.
Thanks,
srinivas

Report message to a moderator

Re: moths and dates [message #228894 is a reply to message #228892]

Wed, 04 April 2007 08:38

Frank
Messages: 7901
Registered: March 2000

Senior Member

I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS
I MUST USE CODE TAGS

Read this post before posting anything else.
Use the testforum or the preview button before ever posting any code again PLEASE!

[Updated on: Wed, 04 April 2007 08:39]

Report message to a moderator

Re: moths and dates [message #228897 is a reply to message #228894]

Wed, 04 April 2007 08:50

pavuluri
Messages: 247
Registered: January 2007

Senior Member


select to_char(dates,'Month'), max(substr(sys_connect_by_path(day,','),2)) days
from
(
select t.dates ,days.day
from 
(
select trunc(sysdate) dates , to_char(sysdate,'DD') days from dual
)t,
(
select rownum day from dict where rownum<=31) days 
where days.day<=t.days
)
start with day=1
connect by prior dates=dates and day =prior day +1
group by dates

iam geting only

april 1,2,3,4

i want to

jan1,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,31"

feb 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"

march        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,31

april  1,2,3,4.

Report message to a moderator

Re: moths and dates [message #228899 is a reply to message #228897]

Wed, 04 April 2007 08:52

pavuluri
Messages: 247
Registered: January 2007

Senior Member

i think

now it is ok

Report message to a moderator

Re: moths and dates [message #228901 is a reply to message #228892]

Wed, 04 April 2007 08:55

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I prefer:

SQL> col days format a84
SQL> with
  2    days as ( select rownum d from dual connect by level <= 31 ),
  3    months as ( select rownum m from dual connect by level <= extract(month from sysdate) ),
  4    data as (
  5      select m, d, count(*) over (partition by m) cnt
  6      from months, days
  7      where (     m < extract(month from sysdate)
  8              and d <= 
  9                   extract(day from last_day(to_date('01'||to_char(m,'00')||to_char(sysdate,'YYYY'),
 10                                                     'DDMMYYYY'))) )
 11            or (   m = extract(month from sysdate)
 12               and d <= extract(day from sysdate) )
 13    )
 14  select to_char(to_date(to_char(m,'00')||to_char(sysdate,'YYYY'),'MMYYYY'),'Mon') month,
 15         substr(sys_connect_by_path(d,','),2) days
 16  from data
 17  where d = cnt
 18  connect by prior m = m and prior d = d-1
 19  start with d = 1
 20  order by m
 21  /
MON DAYS
--- ------------------------------------------------------------------------------------
Jan 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,31
Feb 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
Mar 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,31
Apr 1,2,3,4

4 rows selected.

Regards
Michel

Report message to a moderator