Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1240
  • 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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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