How to strip out the timestamps in the date field (not from a date field but rather a varchar holding date/timestamp data)

Question

So I have data that looks like this:

DATE
2019 04 19 03:00:00

I want it to look like this:

DATE
2019 04 19

OR

DATE
04-19-2019 

The only catch is the field holding the data is a varchar so what I tried below doesn't work.

select to_char(DATE, 'YYYY/MM/DD') as dateonly, from table_name;

I've also tried using the following:

select trunc(DATE) as date_only from table_name;

But I get values that look like this:

 2019 04 19 00:00:00

I guess the above could work but is there a way I can get rid of the trailing 00:00:00s that result from the trunc method that I used above?

Answer 1

First, convert to a DATE:

SELECT TO_DATE('2019 04 19 03:00:00', 'YYYY MM DD HH24:MI:SS') FROM dual;

Then, from a DATE you can use TRUNC() or you can use TO_CHAR() to format it the way you want it to appear:

SELECT TO_CHAR( TO_DATE('2019 04 19 03:00:00', 'YYYY MM DD HH24:MI:SS'), 'YYYY MM DD' ) FROM dual;

Hope this helps.

Answer 2

If your data format is always like yyyy mm dd hh24:mi:ss then you can use following query to get the sub string:

SELECT SUBSTR('2019 04 19 03:00:00', 1, 10)

Ref: https://docs.oracle.com/database/121/SQLRF/functions196.htm#S

Answer 3

You have text. You want a date.

So use TO_DATE.

select trunc(to_date('2019 04 19 03:00:00','YYYY MM DD HH:MI:SS') )
from dual;

The trunc will remove the time period. And then you'll have your data in a proper DATE format so you can use all of the date specific functions offered in the database.