OraFAQ Forum: SQL & PL/SQL » How to sort date by month (JAN) and year(2009)

Home » SQL & PL/SQL » SQL & PL/SQL » How to sort date by month (JAN) and year(2009)

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

How to sort date by month (JAN) and year(2009) [message #459355]

Fri, 04 June 2010 08:42

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi,

I've done this once before, but can't seem to find the sql.

How can I sort by month and year on a column called ex: TEST_dATE

JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010

Thanks,
Joe

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459356 is a reply to message #459355]

Fri, 04 June 2010 08:47

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

Senior Member
Account Moderator

ORDER BY TEST_DATE?

If not, post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459363 is a reply to message #459356]

Fri, 04 June 2010 09:15

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi Michel,

Sorry, I'm new to this forum and thanks for the quick reply.

Ok, here is the info requested. We are using Oracle Database 10g Enterprise Edition Release 10.2.0.2.0.

This would be the test scenerio we could use.

=================================

CREATE TABLE TEST_SORT
(START_DATE DATE)

insert into test_sort values ('22-JAN-2008')

insert into test_sort values ('01-MAR-2006')

insert into test_sort values ('15-DEC-2006')

insert into test_sort values ('10-DEC-2006')

insert into test_sort values ('30-APR-2010')

insert into test_sort values ('28-APR-2009')

insert into test_sort values ('28-APR-2007')

SELECT TO_CHAR(START_DATE, 'MON-YYYY') START_DATE FROM TEST_SORT
ORDER BY 1

START_DATE
==========
APR-2007
APR-2009
DEC-2006
DEC-2006
JAN-2008
MAR-2006

This obviously doesn't sort correctly.

RESULT SHOULD BE:

MAR-2006
DEC-2006
DEC-2006
APR-2007
JAN-2008
APR-2009
APR-2010

Thanks for your help...

Joe

[Updated on: Fri, 04 June 2010 09:22]

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459366 is a reply to message #459363]

Fri, 04 June 2010 09:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT TO_CHAR(START_DATE, 'YYYY-MM') START_DATE FROM TEST_SORT
ORDER BY 1

[Updated on: Fri, 04 June 2010 09:18]

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459369 is a reply to message #459366]

Fri, 04 June 2010 09:24

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi,

Yes, that works fine. THANK YOU !!

Can the MM be converted to MON instead of numbers? If not, then this will work out great.

SELECT TO_CHAR(START_DATE, 'YYYY-MM') START_DATE FROM TEST_SORT
ORDER BY 1

START_DATE
2006-03
2006-12
2006-12
2007-04
2008-01
2009-04
2010-04

Joe

[Updated on: Fri, 04 June 2010 09:25]

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459371 is a reply to message #459369]

Fri, 04 June 2010 09:28

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

SELECT TO_CHAR(START_DATE, 'MON-YYYY') new_START_DATE FROM TEST_SORT
ORDER BY start_date;

You need to order by the date column itself rather than the char representation of it contents.

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459373 is a reply to message #459369]

Fri, 04 June 2010 09:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can the MM be converted to MON instead of numbers? If not, then this will work out great.
Consider MONTH as string such that "APR" sorts before "FEB" because "A" has a smaller value than "F".
MONTH names do not naturally sort in desired order, MONTH by NUMBER orders as desired.

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459374 is a reply to message #459371]

Fri, 04 June 2010 09:32

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

COOKIEMONSTER, BLACKSWAN and MICHEL....

Thanks for your help.

This works out perfect .

SELECT TO_CHAR(START_DATE, 'MON-YYYY') new_START_DATE FROM TEST_SORT
ORDER BY start_date;

I like this forum. I remember joining this, or something similar to FAQ) maybe 10+ years ago, but it was a little different back then. I'm keeping the link handy. You people are fast.

Thanks all.

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459383 is a reply to message #459363]

Fri, 04 June 2010 09:59

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

You are inserting string to a field which is incorrect

Use to_date like below

insert into test_sort values to_date('22-JAN-2008','DD-MON-YYYY');

Regards
Ved

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459388 is a reply to message #459383]

Fri, 04 June 2010 10:17

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi,

Actually, we chose blackswans SELECT TO_CHAR(START_DATE, 'YYYY-MM') START_DATE FROM TEST_SORT
ORDER BY 1

It sorted better that way.

Thanks again, all of you.

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459389 is a reply to message #459388]

Fri, 04 June 2010 10:24

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Good that you have got your solution.
But Ved is saying that while you are inserting date values, use to_date instead of relying on the implicit conversion.

By
Vamsi

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459390 is a reply to message #459389]

Fri, 04 June 2010 10:26

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi Vamsi,

Yes, I saw my insert mistake and understood what Ved was saying. That was a good catch on his part.

Thank you,
Joe

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459392 is a reply to message #459390]

Fri, 04 June 2010 10:36

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Quote:

That was a good catch on his part.

Was just pointing where you were wrong and thats what Orafaq is for.

Regards,
Ved

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459393 is a reply to message #459392]

Fri, 04 June 2010 10:38

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Thanks, Ved.

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459396 is a reply to message #459388]

Fri, 04 June 2010 11:06

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

rappaj wrote on Fri, 04 June 2010 16:17

Hi,

Actually, we chose blackswans SELECT TO_CHAR(START_DATE, 'YYYY-MM') START_DATE FROM TEST_SORT
ORDER BY 1

It sorted better that way.

Thanks again, all of you.

In what way does that sort better?

Report message to a moderator

Re: How to sort date by month (JAN) and year(2009) [message #459412 is a reply to message #459388]

Fri, 04 June 2010 15:05

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

rappaj wrote on Fri, 04 June 2010 08:17

Actually, we chose blackswans SELECT TO_CHAR(START_DATE, 'YYYY-MM') START_DATE FROM TEST_SORT
ORDER BY 1

It sorted better that way.

If you did it right, the sort order is the same as demonstrated below. However, the second one allows usage of a different display format. If you got different results, I am guessing that you failed to supply a column alias that was different from the original column name, so that it ordered by the formatted column, instead of the original.

SCOTT@orcl_11g> SELECT TO_CHAR (start_date, 'YYYY-MM') start_date
  2  FROM   test_sort
  3  ORDER  BY 1
  4  /

START_D
-------
2006-03
2006-12
2006-12
2007-04
2008-01
2009-04
2010-04

7 rows selected.

SCOTT@orcl_11g> SELECT TO_CHAR (start_date, 'MON-YYYY') new_start_date
  2  FROM   test_sort
  3  ORDER  BY start_date
  4  /

NEW_STAR
--------
MAR-2006
DEC-2006
DEC-2006
APR-2007
JAN-2008
APR-2009
APR-2010

7 rows selected.

SCOTT@orcl_11g>

Report message to a moderator

Previous Topic:	trigger for Deleting
Next Topic:	Time zone aware

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 07:39:38 CST 2024