asked on

ORACLE: Where clause, compare datetime to String value

Hello,
I have the following query:

select po_number
, pr_number
       , line_number
       , creationdate
       , row_key
       , count(*) rowcnt
from history_po_detail_charge
-- ** datetime > string ****** --
where creationdate > to_date('01012006000001', 'mmmmyyyyhhmmss')
group by po_number, pr_number, line_number, creationdate, row_key;

I keep getting Oracle error ORA-01810 - format code appears twice.

How can I make this query succeed?

Thanks

paquicuba

Error: 'mmmmyyyyhhmmss' Make it: 'mmddyyyyhhmiss'

paquicuba

Actually, it should be: 'mmddyyyyhh24miss'

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fshtank

ASKER

Dude! Yo da man!
the mmmm was a type-o.

hh24 !? Hmm. Now that one is interesting.

paquicuba

The Error: format code appears twice. was originated by the use of 'mm' in the time part of the format string.

'hhmmss' --<<--- See the 'mm', it is used for the month not for the minutes. For the minutes you must use 'mi'

fshtank

ASKER

Yeah, I did a Homer Simpson "DOH!" slapping my forehead on that one. T-SQL (my normal SQL flavor) formats it 'MM/DD/YYYY HH:MM:SS' and can parse it correctly. (Any variation on MM/DD .. DD/MM or YYYY-MM-DD etc. is acceptable.) Translating between the two is a pain.