Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

to_date function AM PM Format

Tags:

sql

plsql

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'));
like image 942
Arav Avatar asked Jun 06 '13 01:06

Arav


People also ask

What is TO_DATE function?

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.

How do I add AM or PM in Oracle?

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.

How do I change date format from YYYY MM DD in Oracle?

Just use: select to_date(date_value, 'yyyy-mm-dd') as date_value from table; To convert to a date. That's it!


1 Answers

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

like image 107
peterm Avatar answered Sep 30 '22 11:09

peterm