Extract Date with a Timestamp with timezone

Question

How do I convert the following timestamps to a simple date?

Thu Nov 29 18:00:00 CST 2018
Thu Apr 26 01:00:00 BST 2018

Please specify what exactly do you mean by "convert"ing. Are you storing these in a timestamp column or are they stored as strings? — Kaushik Nayak, Sep 18 '18 at 05:27
What to you is a simple date? Asking because I don’t think this is a concept known in Oracle SQL. — Ole V.V., Sep 18 '18 at 08:18

score -1 · Answer 1 · answered Sep 18 '18 at 04:54

-1

this will work:

select trunc(systimestamp), systimestamp from dual;

can also use :

Select to_char(to_date('15-JUN-16 22:54:39','DD-MON-YY 
HH24:MI:SS'),'DD-MON-YY') from dual;

answered Sep 18 '18 at 04:54

Nikhil S

3,786
4
18
32

Ajay Venkata Raju · Accepted Answer · 2018-09-19T05:19:29.290

this code actually splits the data by space and extracts the values from specified index if you assume the space separated values as an array. and then combine it and converts it to date format

SELECT to_date(
       REGEXP_SUBSTR('Thu Nov 29 18:00:00 CST 2018', '[^ ]+', 1, 3)  ||' '||
       REGEXP_SUBSTR('Thu Nov 29 18:00:00 CST 2018', '[^ ]+', 1, 2)  ||' '||
      REGEXP_SUBSTR('Thu Nov 29 18:00:00 CST 2018', '[^ ]+', 1, 6),'DD-MM-YYYY' )
FROM dual ;

you may also use like this:

SELECT to_date(
       REGEXP_SUBSTR(columnname, '[^ ]+', 1, 3)  ||' '||
       REGEXP_SUBSTR(columnname, '[^ ]+', 1, 2)  ||' '||
      REGEXP_SUBSTR(columnname, '[^ ]+', 1, 6),'DD-MM-YYYY' )
FROM tablename;

updated code from your comments to add HH:MM AM/PM cst/gst:

SELECT to_char(to_timestamp(
       REGEXP_SUBSTR('Thu Nov 29 18:00:00 CST 2018', '[^ ]+', 1, 3)  ||' '||
       REGEXP_SUBSTR('Thu Nov 29 18:00:00 CST 2018', '[^ ]+', 1, 2)  ||' '||
       REGEXP_SUBSTR('Thu Nov 29 18:00:00 CST 2018', '[^ ]+', 1, 6)  ||' '||
       REGEXP_SUBSTR('Thu Nov 29 18:00:00 CST 2018', '[^ ]+', 1, 4)
      ,'DD-MM-YYYY hh24:mi:ss' ),'DD-MM-YYYY HH:MI AM'  )||' '||
       REGEXP_SUBSTR('Thu Nov 29 18:00:00 CST 2018', '[^ ]+', 1, 5)     
FROM dual ;

note: this would work only if all the data in your column is in above format.

the output would be a simple date like this: '29-11-2018' if you want the output to be '29-NOV-2018' you can replace the code with 'DD-MON-YYYY'

What if i want to add 8:00 AM GMT to the extracted date ? – Divya Rao Sep 18 '18 at 10:13 — Divya Rao, Sep 18 '18 at 10:13

Extract Date with a Timestamp with timezone

2 Answers2