7

Reading from a csv file using perl and inserting into a oracle table. In a particular csv field the date can be either in AM or PM format. So when i construct the to_date I am using AM / PM but it's giving me format code error. What formatcode needs to be provided in to_date to accept AM/PM Fields.

insert into invoices(invoice_id,invoice_date) values (2,to_date('2010-Aug-09 12:00:01 PM' , 'yyyy-Mon-dd HH:MI:SS AM / PM'));
Arav
  • 4,957
  • 23
  • 77
  • 123
  • 1
    Just as `yyyy` stands for "any four digit year" and `MI` stands for "any two-digit month", `AM` stands for "either AM or PM". You don't need to supply both possibilities in the format string. – Jeffrey Kemp Jun 06 '13 at 05:08
  • MI is minutes, not month – plyawn Mar 27 '15 at 18:36
  • Possible duplicate of [Oracle to\_date with p.m./a.m](http://stackoverflow.com/questions/27269903/oracle-to-date-with-p-m-a-m) – Tony L. Oct 02 '16 at 01:30

1 Answers1

14

You can specify either AM or PM. Try 

SELECT to_date('2010-Aug-09 02:00:01 PM' , 'yyyy-Mon-dd HH:MI:SS AM') "date"
  FROM dual;
SELECT to_date('2010-Aug-09 03:00:01 AM' , 'yyyy-Mon-dd HH:MI:SS PM') "date"
  FROM dual;

Output:

|                          DATE |
---------------------------------
| August, 09 2010 14:00:01+0000 |

|                          DATE |
---------------------------------
| August, 09 2010 03:00:01+0000 |

Here SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157