Thursday 26 November 2009

Oracle Dates confusement

I know that confusement (sp??) is not a real word, but I have heard it used by a lot of people, so I think that it should be a real word.

Anyway, because my experiences of oracle have been mainly with JDE, I don’t understand the oracle date type very well.  Say I want to group data by an oracle date day, how do I do that???  Because the oracle date secretly contains time…

With the adoption of UTIME, I need to update my skills to be able to take this on…  So google search here I come.

My query is simple, I want to fine all of the records in the F90710 that seem to be sticking.  Ie. the trigger message is not making it to the transaction server for these messages and they are stuck!!!

so I want something like:

select count(1), ETEVNTST, ETEVNTNAME, ETEVNTTIME from sy812.f90710 group by ETEVNTNAME,ETEVNTST, ETEVNTTIME

And I get many separate lines for every day, so I need to convert the date to a day I guess.

select count(1), ETEVNTST, ETEVNTNAME, trunc(ETEVNTTIME, 'MONTH')
from sy812.f90710
group by ETEVNTNAME,ETEVNTST, trunc(ETEVNTTIME, 'MONTH')

This did nothing for me, I think it was grouped by month.  A little handy I guess.  I tried using ‘DAY’ in the TRUNC function, was not what I needed.

So, am going to try:

select count(1), ETEVNTST, ETEVNTNAME, to_char(ETEVNTTIME, 'DDMMYY')
from sy812.f90710
group by ETEVNTNAME,ETEVNTST, to_char(ETEVNTTIME, 'DDMMYY')

That’s the ticket.  The use of tochar is awesomely simple. HH:MM:SS are all available.

No comments: