Home » SQL & PL/SQL » SQL & PL/SQL » moths and dates
moths and dates [message #228830] Wed, 04 April 2007 06:12 Go to next message
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
Re: moths and dates [message #228842 is a reply to message #228830] Wed, 04 April 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: moths and dates [message #228843 is a reply to message #228842] Wed, 04 April 2007 06:52 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
ok great
thanks
srinivas
Re: moths and dates [message #228854 is a reply to message #228843] Wed, 04 April 2007 07:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you even read the replies you get?
Re: moths and dates [message #228858 is a reply to message #228854] Wed, 04 April 2007 07:30 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry
thanks
srinivas
Re: moths and dates [message #228867 is a reply to message #228858] Wed, 04 April 2007 08:04 Go to previous messageGo to next message
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.
Re: moths and dates [message #228869 is a reply to message #228867] Wed, 04 April 2007 08:07 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
month is column and dates is another column
thanks
srinivas
Re: moths and dates [message #228873 is a reply to message #228869] Wed, 04 April 2007 08:10 Go to previous messageGo to next message
joy_division
Messages: 4644
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.
Re: moths and dates [message #228876 is a reply to message #228869] Wed, 04 April 2007 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many dots do you want to display?

Regards
Michel
Re: moths and dates [message #228877 is a reply to message #228873] Wed, 04 April 2007 08:17 Go to previous messageGo to next message
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
Re: moths and dates [message #228881 is a reply to message #228877] Wed, 04 April 2007 08:20 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Still no CODE tags.
Re: moths and dates [message #228882 is a reply to message #228881] Wed, 04 April 2007 08:22 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry joy_division

really i dont know CODE tags
Re: moths and dates [message #228886 is a reply to message #228882] Wed, 04 April 2007 08:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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


Re: moths and dates [message #228894 is a reply to message #228892] Wed, 04 April 2007 08:38 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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.









Re: moths and dates [message #228899 is a reply to message #228897] Wed, 04 April 2007 08:52 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i think

now it is ok
Re: moths and dates [message #228901 is a reply to message #228892] Wed, 04 April 2007 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: moths and dates [message #228921 is a reply to message #228901] Wed, 04 April 2007 09:27 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
please send me single query.
thanks,
srinivas
Re: moths and dates [message #228947 is a reply to message #228921] Wed, 04 April 2007 10:59 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Shocked What do you mean Question

Previous Topic: union keyword
Next Topic: Spot the error
Goto Forum:
  


Current Time: Fri Dec 09 15:55:33 CST 2016

Total time taken to generate the page: 0.29645 seconds