| Oracle SQL [message #609794] |
Wed, 12 March 2014 07:57  |
 |
Hometown1
Messages: 23
Registered: March 2011
|
Junior Member |
|
|
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production
1 SELECT TRUNC(To_date(To_char(donation_date, 'Month-YYYY'), 'Month-YYYY')),
2 SUM(TO_CHAR(category_amount,'999999.99'))
3 FROM tbl_donation,
4 tbl_category_detail
5 WHERE tbl_donation.donation_code = tbl_category_detail.donation_code
6 AND donation_date BETWEEN '01-Apr-13' AND '31-Mar-14'
7 AND tbl_donation.currency_code = '1'
8 GROUP BY TRUNC(To_date(To_char(donation_date, 'Month-YYYY'), 'Month-YYYY'))
9* ORDER BY TRUNC(To_date(To_char(donation_date, 'Month-YYYY'), 'Month-YYYY'))
SQL> /
TRUNC(TO_ SUM(TO_CHAR(CATEGORY_AMOUNT,'999999.99'))
--------- -----------------------------------------
01-APR-13 88652.92
01-MAY-13 54498.74
01-JUN-13 49352.04
01-JUL-13 685293.2
01-AUG-13 622952.6
01-SEP-13 84606.05
01-OCT-13 186125.06
01-NOV-13 68202.5
01-DEC-13 62623.86
01-JAN-14 65520.62
01-FEB-14 50194.25
01-MAR-14 3361.68
I want to retrieve the result set in the below manner, but without changing nls_date_format. I know if I alter session then I can retrieve in the desired format
APRIL-13 88652.92
MAY-13 54498.74
JUNE-13 49352.04
JULY-13 685293.2
AUGUST-13 622952.6
SEPTEMBER-13 84606.05
OCTOBER-13 186125.06
NOVEMBER-13 68202.5
DECEMBER-13 62623.86
JANUARY-14 65520.62
FEBRUARY-14 50194.25
MARCH-14 3361.68 |
|
|
|
|
|
| Re: Oracle SQL [message #609797 is a reply to message #609796] |
Wed, 12 March 2014 08:19  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
To_char(donation_date, 'fmMonth-YY)
If you want to display a date in a particular format then you have to convert it to a string.
You're doing that but then converting it back to a date, which removes the formatting.
|
|
|
|
|
|
| Re: Oracle SQL [message #609800 is a reply to message #609797] |
Wed, 12 March 2014 08:27 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Of course the char version of the date doesn't sort correctly. To get around that you need to convert it back to a date in the order by clause
|
|
|
|
|
|
|
|
| Re: Oracle SQL [message #609809 is a reply to message #609807] |
Wed, 12 March 2014 09:53 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> col nop noprint
SQL> select trunc(hiredate,'month') nop , to_char(hiredate,'Mon-YYYY') mnth, count(*)
2 from emp
3 group by trunc(hiredate,'month'), to_char(hiredate,'Mon-YYYY')
4 order by 1
5 /
MNTH COUNT(*)
-------- ----------
Dec-1980 1
Feb-1981 2
Apr-1981 1
May-1981 1
Jun-1981 1
Sep-1981 2
Nov-1981 1
Dec-1981 2
Jan-1982 1
Apr-1987 1
May-1987 1
|
|
|
|
| Re: Oracle SQL [message #609811 is a reply to message #609809] |
Wed, 12 March 2014 10:13 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's another way. I was thinking:
group by to_char(hiredate,'Mon-YYYY')
order by to_date(to_char(hiredate,'Mon-YYYY'), 'Mon-YYYY')
|
|
|
|
|
|
| Re: Oracle SQL [message #609819 is a reply to message #609814] |
Wed, 12 March 2014 13:12 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Or:
select to_char(trunc(hiredate,'month'),'Mon-YYYY') mnth,
count(*)
from emp
group by trunc(hiredate,'month')
order by trunc(hiredate,'month')
/
MNTH COUNT(*)
-------- ----------
Dec-1980 1
Feb-1981 2
Apr-1981 1
May-1981 1
Jun-1981 1
Sep-1981 2
Nov-1981 1
Dec-1981 2
Jan-1982 1
Apr-1987 1
May-1987 1
11 rows selected.
SQL>
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
