Help with Oracle GROUP BY on dates

[Guest]

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

[Newsgroup_User]

> 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

[paross1]

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