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
fshtankAsked:
Who is Participating?
 
paquicubaConnect With a Mentor Commented:
Your complete 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', 'mmddyyyyhh24miss')
group by  po_number, pr_number, line_number, creationdate, row_key;
0
 
paquicubaCommented:
Error: 'mmmmyyyyhhmmss'    Make it: 'mmddyyyyhhmiss'
0
 
paquicubaCommented:
Actually, it should be:  'mmddyyyyhh24miss'
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

hh24 !?    Hmm.  Now that one is interesting.
0
 
paquicubaCommented:
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'
0
 
fshtankAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.