• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1247
  • Last Modified:

how to change datetime format from dataset and store into oracle

Hi expert,

I have the dataset which contains datatime formated  as 12/06/2008 12:00:00, I need to reformat it as 12-June-2008 to store into ORACLE database, otherwise it give me error as ORA-01861: literal does not match format string

can some one give me advise how to solve this issue.

Thanks in advance

Ying
0
ying88888
Asked:
ying88888
  • 2
1 Solution
 
MikeOM_DBACommented:
Oracle does not store dates in any particular format.

To have Oracle display dates in a specific format, set the NLS_DATE_FORMAT parameter:

ALTER SYSTEM SET NLS_DATE_FORMAT='DD-Month-YYYY';

Open in new window

0
 
MikeOM_DBACommented:
...OR Alter session.
If you have an external dataset in a particular format, use the TO_DATE() function to load into an oracle date column:



SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-Month-YYYY';
 
Session altered.
 
SQL> SELECT TO_DATE('12/06/2008 12:00:00','DD/MM/YYYY HH24:MI:SS') FROM DUAL;
 
TO_DATE('12/06/20
-----------------
12-June-2008
 
SQL> 

Open in new window

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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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