2 Replies Latest reply on Oct 14, 2008 2:15 PM by Newsgroup_User

    Help with Oracle GROUP BY  on dates

    Gary1 Level 1
      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
        • 1. Re: Help with Oracle GROUP BY  on dates
          Level 7
          > 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
          • 2. Re: Help with Oracle GROUP BY  on dates
            paross1 Level 2
            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