Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Kavya.

Asked: June 07, 2018 - 10:26 am UTC

Last updated: June 11, 2018 - 7:08 am UTC

Version: Version 3.0.04

Viewed 1000+ times

You Asked

I am having Column value as 'YYYYDDMM' and need to convert into 'DD/MM/YYYY' and store the data.

and Connor said...

OK, I'm assuming that both the input and the output are strings. So you just need to nest a couple of functions - one to take the input into a date, and one to take it back to a string

SQL> create table t ( x varchar2(10));

Table created.

SQL>
SQL> insert into t values ('20180327');

1 row created.

SQL>
SQL> select
  2    to_char(
  3       to_date(x,'yyyymmdd'),'dd/mm/yyyy') from t;

TO_CHAR(TO
----------
27/03/2018


By the way, check out Chris's video why you don't want to store dates as strings...its bad




So ideally, you just want to store your dates and dates - and only format them a strings when you need to present them to the user (on screen or in reports etc)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library