0

I am trying to create a monthly report for easy review and comparison of runtimes of certain processes.

The raw data needed for that is stored in a Oracle 11g database table, with the runtime being the difference between STARTDATE and ENDDATE which are both formatted as DD.MM.YYYY HH24:MI:SS.

One dateset is created for each run of a process, so there is a huge number of them stored in that table (several datasets per process per hour).

Now normally this would be pretty straight forward by just using a GROUP BY clause, however, I couldn't get it to work in this case. Most likely due to the DATE columns being involved with their special format.

Is there any possibility to group the data sets by the month which is stored within the STARTDATEcolumn? I already tried using some Oracle date functions which I found via googling, e.g. MONTH() and EXTRACT() , but so far I didn't get anywhere with it.

I would greatly appreciate any hints regarding this issue.

Thanks in advance and best regards, daZza

daZza
  • 1,669
  • 1
  • 29
  • 51
  • 1
    Which query did you try? The MONTH function should work fine (but you also need the YEAR) – JotaBe Sep 23 '14 at 09:23
  • I tried `SELECT ... FROM ... WHERE ... GROUP BY MONTH(STARTDATE);` This threw an month is a invalid identifier exception though. – daZza Sep 23 '14 at 09:29
  • DATE columns do not have a format in the table. They have an internal representation and then how it is formatted when you query them depend on the settings of your client. – Kim Berg Hansen Sep 23 '14 at 09:29
  • GROUP BY TRUNC(STARTDATE,'MM') would be a possibility. – Kim Berg Hansen Sep 23 '14 at 09:30
  • @KimBergHansen Well, they are formatted by the `nls_date_format` setting, which in this case is `DD.MM.YYYY HH24:MI:SS` – daZza Sep 23 '14 at 09:31
  • @KimBergHansen I'll try that `TRUNC` thingy. Sounds promising – daZza Sep 23 '14 at 09:37
  • 1. There is no MONTH function in Oracle SQL. There is a MONTH Operator for the EXTRACT function though. 2. Kim Berg Hansen is right. With nls_date_format you say how you want a date be *shown* when not naming a date format explicitely. It has nothing to do with how dates are *stored* in the table. – Thorsten Kettner Sep 23 '14 at 10:09

1 Answers1

0

To get a dates month in Oracle you can convert that part to a string and use this:

select 
  to_char(startdate, 'yyyymm') as startmonth,
  ....
from ...
group by to_char(startdate, 'yyyymm');
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • 1
    The select of it looks good, excatly what I need. However, the `GROUP BY`clause throws an error: `"not a GROUP BY expression"` – daZza Sep 23 '14 at 11:11
  • I suppose you select some other none-aggregated value. Everything you select must either be in GROUP BY or aggregated (MIN, MAX etc.). So `select a, max(b), c from mytable group by a` throws an error, because c is neither in GROUP BY nor being aggregated. Check your query. – Thorsten Kettner Sep 23 '14 at 11:14
  • Well, that sucks. If I group by everything I select the grouping doesn't make sense anymore. The only thing I want to group by is the month, while keeping all the other data in its original form (except for being grouped by the month). I'll edit the OP with a data sample, maybe it get clearer then. Give me a minute – daZza Sep 23 '14 at 11:18
  • Well nevermind, I looked over the structure again and found a way. Thanks! – daZza Sep 23 '14 at 11:24