[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5588
  • Last Modified:

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
0
fshtank
Asked:
fshtank
  • 4
  • 2
1 Solution
 
paquicubaCommented:
Error: 'mmmmyyyyhhmmss'    Make it: 'mmddyyyyhhmiss'
0
 
paquicubaCommented:
Actually, it should be:  'mmddyyyyhh24miss'
0
 
paquicubaCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now