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'));
The TO_DATE function accepts an argument of a character data type and converts this value to a DATETIME value. The TO_DATE function evaluates a character string according to the date-formatting directive that you specify and returns a DATETIME value.
When using the TO_CHAR() function to format a datetime value in Oracle Database, you can add the meridiem indicator (AM/PM) by simply adding either AM or PM to your format model. Oracle then displays the appropriate meridiem indicator, depending on whether the time value is AM or PM.
Just use: select to_date(date_value, 'yyyy-mm-dd') as date_value from table; To convert to a date. That's it!
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With