0

I have date like 'Mon Sep 14 09:03:10 +0000 2015', that I did not succeed in transforming them into date format

Already tried to_date/to_timestamp and different format spec

For example: 

select TO_TIMESTAMP_TZ('Mon Sep 14 09:03:10 +0000 2015') from dual;

I expect to retrieve something like 'DD/MM/YYYY'.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Tristan Salord
  • 57
  • 1
  • 10

3 Answers3

0

This should work in Oracle:

select TO_TIMESTAMP_TZ('Mon Sep 14 09:03:10 +0000 2015', 'DY MON dd HH24:MI:SS TZHTZM YYYY')
from dual;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

In Oracle:

select 
TO_CHAR(CAST(SUBSTR(TO_TIMESTAMP_TZ('Mon Sep 14 09:03:10 +0000 2015', 'DY Mon dd HH24:MI:SS TZHTZM YYYY'),1,9) AS DATE),'DD/MM/YYYY')
from dual;

Output:

14/09/2015
Gen Wan
  • 1,979
  • 2
  • 12
  • 19
  • Thank you very much Gen Wan.... Could you explain why is it necessary to use cast? Perfect query!!!! thx a lot!!! – Tristan Salord Apr 11 '19 at 06:43
  • @TristanSalord Because I used TO_CHAR function to convert it to a specific format and it requires that the expression is datetime or similar data type. ( I cast it as date). If you think my answer works for you, please click the check button below 'vote up/down' to accept this answer. – Gen Wan Apr 11 '19 at 15:41
0

Ok so Gen Wan solution works pretty fine and at the end i was near the solution but i had to change my sqldevelopper language to english in order to work. If you use Sql developper in other language it won't recognize the timestamp format answearing "this is not a valid day of the week".

So Solution of Gen Wan : select TO_CHAR(CAST(SUBSTR(TO_TIMESTAMP_TZ('Mon Sep 14 09:03:10 +0000 2015', 'DY Mon dd HH24:MI:SS TZHTZM YYYY'),1,9) AS DATE),'DD/MM/YYYY') from dual;

Thanks to him a lot!

Tristan Salord
  • 57
  • 1
  • 10