Now i am in a condition, Where I am just displaying the month names and some data grouped by month number. There is a small issue. I need to get month names instead of month numbers from Oracle.
here's my query
SELECT Count(*),EXTRACT (MONTH FROM DATE_CREATED),EXTRACT (YEAR FROM DATE_CREATED) FROM MyTable GROUP BY EXTRACT (YEAR FROM DATE_CREATED) ,EXTRACT (MONTH FROM DATE_CREATED)
And the result is 3 columns. Count, Month(Number) and year
something like this
3 10 2006
Please anyone help to convert the month number to month name in oracle or C#. Problem is I cannot use the data formatting functions as this is a single number appearing.
Select count(*), EXTRACT (MONTH FROM DATE_CREATED),to_char(date_created,'MONTH')month_created FROM MyTable WHERE EXTRACT (YEAR FROM DATE_CREATED) = '2007' GROUP BY EXTRACT (MONTH FROM DATE_CREATED) ,to_char(date_created,'MONTH')
I know I ma being crazy but now the rek has been modified to display all the columns irespective of if they have any data or not. They want to see all the months even if the data against them is zero!!!!!!!!!!!!!!!!
Thanks for the reply, I found something to my rescue. Actually when we were doing a select on the data, it was not showing up the months where there no data. So I did a join on two tables-one table was my original table and the second was the dual table.
I putting my query here for your reference.
select A.months_name ,B.Data, A.Month_Number from ( select to_char(add_months(trunc(sysdate,'YEAR'),rownum-1),'MONTH') months_name , to_char(add_months(trunc(sysdate,'YEAR'),rownum-1),'MM') month_number from dual connect by rownum<=12 ) A , ( Select
count(*) Data, EXTRACT (MONTH FROM DATE_CREATED) Month_Number ,to_char(date_created,'MONTH')month_created FROM MyTable WHERE EXTRACT (YEAR FROM DATE_CREATED) = '2008' GROUP BY EXTRACT (MONTH FROM DATE_CREATED) ,to_char(date_created,'MONTH') ) B WHERE
A.months_name =B.month_created(+) ORDER BY A.Month_Number ;
Now my datagrid appears with empty cell for months which do not have any data associated. Although this solves my problem, but I would still love to see a 0 instead of an empty column.
Champ0308
Member
18 Points
25 Posts
Display Month Names from month numbers
Oct 11, 2007 01:58 PM|LINK
Now i am in a condition, Where I am just displaying the month names and some data grouped by month number. There is a small issue. I need to get month names instead of month numbers from Oracle.
here's my query
SELECT Count(*),EXTRACT (MONTH FROM DATE_CREATED),EXTRACT (YEAR FROM DATE_CREATED) FROM MyTable GROUP BY EXTRACT (YEAR FROM DATE_CREATED) ,EXTRACT (MONTH FROM DATE_CREATED)
And the result is 3 columns. Count, Month(Number) and year
something like this
3 10 2006
Please anyone help to convert the month number to month name in oracle or C#. Problem is I cannot use the data formatting functions as this is a single number appearing.
Thanks for any help!!!!!!!!!!!!!!!
Prashant Kum...
Star
12334 Points
1992 Posts
Re: Display Month Names from month numbers
Oct 11, 2007 02:35 PM|LINK
You can use
Select count(*), to_char(date_created,'mm')month_created, to_char(date_created,'YYYY') year_created from ....
Champ0308
Member
18 Points
25 Posts
Re: Display Month Names from month numbers
Oct 11, 2007 02:51 PM|LINK
Gr8888888888888888888..it worked...thanks genious
Champ0308
Member
18 Points
25 Posts
Re: Display Month Names from month numbers
Oct 11, 2007 02:54 PM|LINK
Another issue..it is grouping by the names now.....i want it to group in the Month order i.e. Jan to Dec...Any ideas.
Thanks
Champ0308
Member
18 Points
25 Posts
Re: Display Month Names from month numbers
Oct 11, 2007 03:00 PM|LINK
I got it!!!!!!!!!!!!!!!!!!!!
Select count(*), EXTRACT (MONTH FROM DATE_CREATED),to_char(date_created,'MONTH')month_created FROM MyTable WHERE EXTRACT (YEAR FROM DATE_CREATED) = '2007' GROUP BY EXTRACT (MONTH FROM DATE_CREATED) ,to_char(date_created,'MONTH')
Thanks Prashant and have a nice day
Champ0308
Member
18 Points
25 Posts
Re: Display Month Names from month numbers
Oct 11, 2007 05:03 PM|LINK
I know I ma being crazy but now the rek has been modified to display all the columns irespective of if they have any data or not. They want to see all the months even if the data against them is zero!!!!!!!!!!!!!!!!
Please any one help...
Thanks
Prashant Kum...
Star
12334 Points
1992 Posts
Re: Display Month Names from month numbers
Oct 12, 2007 08:05 AM|LINK
Can you explain your requirement a bit more?
Champ0308
Member
18 Points
25 Posts
Re: Display Month Names from month numbers
Oct 12, 2007 02:37 PM|LINK
Hi Prashant,
Thanks for the reply, I found something to my rescue. Actually when we were doing a select on the data, it was not showing up the months where there no data. So I did a join on two tables-one table was my original table and the second was the dual table. I putting my query here for your reference.
select A.months_name ,B.Data, A.Month_Number from ( select to_char(add_months(trunc(sysdate,'YEAR'),rownum-1),'MONTH') months_name , to_char(add_months(trunc(sysdate,'YEAR'),rownum-1),'MM') month_number from dual connect by rownum<=12 ) A , ( Select count(*) Data, EXTRACT (MONTH FROM DATE_CREATED) Month_Number ,to_char(date_created,'MONTH')month_created FROM MyTable WHERE EXTRACT (YEAR FROM DATE_CREATED) = '2008' GROUP BY EXTRACT (MONTH FROM DATE_CREATED) ,to_char(date_created,'MONTH') ) B WHERE A.months_name =B.month_created(+) ORDER BY A.Month_Number ;
Now my datagrid appears with empty cell for months which do not have any data associated. Although this solves my problem, but I would still love to see a 0 instead of an empty column.
Thanks
Prashant Kum...
Star
12334 Points
1992 Posts
Re: Display Month Names from month numbers
Oct 12, 2007 06:06 PM|LINK
Something like the following should solve your problem
select nvl(A.Month_Number,'0')Month_Number from ...
Champ0308
Member
18 Points
25 Posts
Re: Display Month Names from month numbers
Oct 12, 2007 08:55 PM|LINK
thanks it works!!!!!!!!!!!!!!!