user5506560 user5506560 - 5 months ago 44
SQL Question

Convert yyyy/mm/dd into dd/mm/yyyy hh24:MI:SS

I'm trying to convert data in format

2016/06/26
into
26/06/2016 00:00:00

I was trying few option all the time getting error "Invalid months name",
Any idea/advice?
Thanks

select to_date('2016/05/07 00:00:00','mm/dd/yyyy HH24:MI:SS') from dual

Answer

In order to convert a string to a date you need to convert it first to a date. Your problems is that you are trying to format a string not a date. So for you specific case it would be:

--convert it first to a date
select to_date('2016/05/07 00:00:00','yyyy/mm/dd HH24:MI:SS') 
  from dual

--then convert it to a string in the format you want:
select to_char( to_date('2016/05/07 00:00:00','yyyy/mm/dd HH24:MI:SS'),
                'mm/dd/yyyy HH24:MI:SS' )
  from dual

--since you want it as a date:

--then convert it to a string in the format you want:
select to_date( to_char( to_date('2016/05/07 00:00:00',
                                 'yyyy/mm/dd HH24:MI:SS'),
                         'mm/dd/yyyy HH24:MI:SS' )
                'mm/dd/yyyy HH24:MI:SS' ) 
  from dual

If you want just to convert your string into a date no matter the format, just use the first select I showed. Thanks to @Boneist in comments for pointing it out.

Comments