Home » SQL & PL/SQL » SQL & PL/SQL » Month from JAN to DEC and not from APR to SEP
Month from JAN to DEC and not from APR to SEP [message #263238] Wed, 29 August 2007 08:29 Go to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
Hi,

I am trying to get the month value for my reporting purpose but the problem is that the month are shown in alphabetic order and not as calender way.

I tried :
to_char(creation_date,'MON')


again i tried :
to_date(to_char(creation_date,'MON'),'MON')

here also I am not getting my desired result.

Please suggest.

Regards,
Satya
Re: Month from JAN to DEC and not from APR to SEP [message #263241 is a reply to message #263238] Wed, 29 August 2007 08:33 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
to_char(creation_date,'MM')
Re: Month from JAN to DEC and not from APR to SEP [message #263242 is a reply to message #263238] Wed, 29 August 2007 08:34 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
It is an expected behaviour, as you are converting that to char.
Try to order by on date itself.

Edit:
Quote:
to_date(to_char(creation_date,'MON'),'MON')
Looks strange. Please provide your entire sql.

By
Vamsi

[Updated on: Wed, 29 August 2007 08:39]

Report message to a moderator

Re: Month from JAN to DEC and not from APR to SEP [message #263247 is a reply to message #263238] Wed, 29 August 2007 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(trunc(created,'MONTH'),'YYYY-MON') "Month", count(*) "Nb" 
  2  from user_objects
  3  group by trunc(created,'MONTH')
  4  order by trunc(created,'MONTH')
  5  /
Month            Nb
-------- ----------
2006-JUN          4
2006-JUL          5
2006-AUG          3
2006-OCT          3
2006-NOV          1
2006-DEC          6
2007-JAN         11
2007-FEB          6
2007-MAR         16
2007-APR         12
2007-MAY         11
2007-JUN         33
2007-JUL         51
2007-AUG         17

14 rows selected.

Regards
Michel
Re: Month from JAN to DEC and not from APR to SEP [message #263407 is a reply to message #263247] Wed, 29 August 2007 23:59 Go to previous messageGo to next message
satya.das2007
Messages: 33
Registered: February 2007
Location: India
Member
I tried but it dint work. Please check the attachment for exact query used.
  • Attachment: month.doc
    (Size: 42.50KB, Downloaded 466 times)
Re: Month from JAN to DEC and not from APR to SEP [message #263424 is a reply to message #263407] Thu, 30 August 2007 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't and don't want to downlog doc file.
Post it in a txt file.

In addition, "I tried but it dint work" is not a valid Oracle error message. What does this mean?

Regards
Michel
Re: Month from JAN to DEC and not from APR to SEP [message #263452 is a reply to message #263407] Thu, 30 August 2007 02:07 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Michel's sql:
Quote:
SQL> select to_char(trunc(created,'MONTH'),'YYYY-MON') "Month", count(*) "Nb"
2 from user_objects
3 group by trunc(created,'MONTH')
4 order by trunc(created,'MONTH')
I don't see yours is same as this.
Don't you see the difference?

By
Vamsi
Re: Month from JAN to DEC and not from APR to SEP [message #263459 is a reply to message #263452] Thu, 30 August 2007 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste the execution as I did (you may skip some output lines if it is too long just keeping the relevant ones).

Regards
Michel
Re: Month from JAN to DEC and not from APR to SEP [message #263611 is a reply to message #263459] Thu, 30 August 2007 08:58 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Quote:
to_date(to_char(creation_date,'MON'),'MON')

This is ordering by date...sort of

For example:
SELECT creation_date
     , TO_DATE(TO_CHAR(creation_date, 'MON'), 'MON') orderby_date
FROM ( SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YY'), ROWNUM - 1) creation_date
       FROM DUAL
       CONNECT BY ROWNUM <= 13 )
ORDER BY TO_DATE(TO_CHAR(creation_date, 'MON'), 'MON');

CREATION_DA ORDERBY_DAT
----------- -----------
01-JAN-2007 01-JAN-2007
01-JAN-2008 01-JAN-2007
01-FEB-2007 01-FEB-2007
01-MAR-2007 01-MAR-2007
01-APR-2007 01-APR-2007
01-MAY-2007 01-MAY-2007
01-JUN-2007 01-JUN-2007
01-JUL-2007 01-JUL-2007
01-AUG-2007 01-AUG-2007
01-SEP-2007 01-SEP-2007
01-OCT-2007 01-OCT-2007
01-NOV-2007 01-NOV-2007
01-DEC-2007 01-DEC-2007

Notice the CREATION_DATE (column 1) for row two is 2008, yet ORDERBY_DATE (column 2) is 2007. The combination of TO_DATE/TO_CHAR in the second column is the issue here. TO_CHAR converts the date to the character string 'JAN'. TO_DATE then converts 'JAN' to a date. With no year information, the function converts the string to a date in the current year.

Michel, on the other hand, maintains the date datatype; therefore, the rows are ordered by the first of the month.
SELECT creation_date
     , TO_DATE(TO_CHAR(creation_date, 'MON'), 'MON') orderby_date
FROM ( SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YY'), ROWNUM - 1) creation_date
       FROM DUAL
       CONNECT BY ROWNUM <= 13 )
ORDER BY TRUNC(creation_date, 'MONTH');

CREATION_DA ORDERBY_DAT
----------- -----------
01-JAN-2007 01-JAN-2007
01-FEB-2007 01-FEB-2007
01-MAR-2007 01-MAR-2007
01-APR-2007 01-APR-2007
01-MAY-2007 01-MAY-2007
01-JUN-2007 01-JUN-2007
01-JUL-2007 01-JUL-2007
01-AUG-2007 01-AUG-2007
01-SEP-2007 01-SEP-2007
01-OCT-2007 01-OCT-2007
01-NOV-2007 01-NOV-2007
01-DEC-2007 01-DEC-2007
01-JAN-2008 01-JAN-2007
Re: Month from JAN to DEC and not from APR to SEP [message #263910 is a reply to message #263238] Fri, 31 August 2007 04:56 Go to previous messageGo to next message
lenin_babu55
Messages: 12
Registered: August 2007
Junior Member
hi dude

check it once................


select to_char(to_date('12-aug-2007','dd-mon-yyyy'),'MON') from dual
Re: Month from JAN to DEC and not from APR to SEP [message #263934 is a reply to message #263238] Fri, 31 August 2007 05:30 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
hi

please replace your query in the following format

sql> select to_char(hiredate,'mon') from scott.emp
2) order by to_Date(to_char(hiredate,'mon'),'mon');

Regards
Re: Month from JAN to DEC and not from APR to SEP [message #264133 is a reply to message #263238] Sat, 01 September 2007 00:36 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select to_char(trunc(creation_date),'MM') mm, to_char(trunc(creation_date),'MON') "month"
from yourtable
group by to_char(trunc(creation_date),'MON'), to_char(trunc(creation_date),'MM')
order by mm
Previous Topic: issue with to_date and to_char
Next Topic: Help Needed this trigger Message
Goto Forum:
  


Current Time: Mon Dec 05 14:52:29 CST 2016

Total time taken to generate the page: 0.10584 seconds