0

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
bazyl
  • 263
  • 1
  • 7
  • 17
  • My guess is that you are using a format string that includes month name instead of month number. – Álvaro González Jun 28 '16 at 11:22
  • show us the command what indicated that error – Thomas Jun 28 '16 at 11:26
  • Hello, I edited my answer – bazyl Jun 28 '16 at 11:33
  • @user5506560 Why do you need to do this? What is the context around this requirement? E.g. where does the date information come from? Is it a file, or a table in the database? What will you do with the results? Are you going to insert the results into another table? Output the results for reporting purposes? – Boneist Jun 28 '16 at 11:58
  • Just seen your edit: isn't there a clear mismatch between `2016/05/07` and `mm/dd/yyyy`? – Álvaro González Jun 30 '16 at 10:25

1 Answers1

1

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.

Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
  • Thanks, but the issue is that at the end I need that value AS date type... – bazyl Jun 28 '16 at 11:53
  • Then convert it to a date again. – Jorge Campos Jun 28 '16 at 11:53
  • I tried but I got error message date format picture ends before converting entire input string" – bazyl Jun 28 '16 at 11:55
  • @JorgeCampos surely, if the OP wants their string as a date, then you just need the initial `to_date` - ie. no need to convert it to a string and then back to a date? – Boneist Jun 28 '16 at 11:57
  • Hm, I tried you edited answer and the result is 16/05/07 – bazyl Jun 28 '16 at 11:58
  • @Boneist You are right. I thought that he just want to change the date format. – Jorge Campos Jun 28 '16 at 11:59
  • @user5506560 That's because your client configuration. – Jorge Campos Jun 28 '16 at 11:59
  • 3
    If you have converted it to a date once then you **do not** need to convert it to a string and back to a date again. Dates do not have a format (they are represented by [7- or 8-bytes internally to the database](http://stackoverflow.com/a/13568348/1509264)) and it only when they are passed to a client program and that client program translates it to whatever format it stores it in does it potentially get a format. – MT0 Jun 28 '16 at 13:01