Skip to main content
A
Anonymous
October 14, 2008
Question

Help with Oracle GROUP BY on dates

  • October 14, 2008
  • 2 replies
  • 3700 views
Just got connectivity to an Oracle DB/Server, and finding out working with dates is VERY different. Been using CF7 for years, writing T-SQL where possible on SQL Server and IBM UDB/DB2. Both, along with almost every language I know, have a MONTH() and YEAR() function. Apparently, Oracle does not. Have Google-searched tons of Oracle date examples, but can't find anything that lets me group by YEAR, MONTH.

I can use "to_char" and get results as OCT-08, SEP-08, etc. But I need to sort (ORDER BY) these. Since they are strings, query results are displayed alphabetically. With APR-08 and AUG-08 always shown first, since these months begin with letter "A".

Can anyone please provide a quick example of how to GROUP BY and ORDER BY a date field, but need it to display and sort GROUP-BY results by YEAR then MONTH? Would appreciate any help with the syntax, while I keep searching online for an example. Thanks.

Gary1
This topic has been closed for replies.

2 replies

P
paross1
Participating Frequently
October 14, 2008
Use the correct "mask" in your to_char function to get the numeric year and month, then convert them to numbers instead of characters using TO_NUMBER..... something like this:

SELECT other_fields, yourdate, TO_CHAR(yourdate, 'MON-YY')
FROM yourtable
ORDER BY TO_NUMBER(TO_CHAR(yourdate, 'yy')), TO_NUMBER(TO_CHAR(yourdate, 'mm'))

--or--

SELECT other_fields, yourdate, TO_CHAR(yourdate, 'MON-YY')
FROM yourtable
ORDER BY TO_NUMBER(TO_CHAR(yourdate, 'yymm'))

Phil
N
Newsgroup_User
Inspiring
October 14, 2008
> I can use "to_char" and get results as OCT-08, SEP-08, etc. But I need to
> sort (ORDER BY) these. Since they are strings, query results are displayed
> alphabetically. With APR-08 and AUG-08 always shown first, since these months
> begin with letter "A".

There are an awful lot more options for formtting dates available to you
than that:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#48515

(That's for 9i...)

If you're grouping / sorting by month, wouldn't you want your YEAR before
your month, anyhow (irrespective of which month format you're using),
otherwise you'd be getting all your Januaries - eg, Jan 2005, Jan 2006, Jan
2007, Jan 2008, etc - listed before your Februaries, etc. I'd be using
YYYY for year and MM for month; that'll give you a six-char string which'd
sort nicely.

--
Adam
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices