[sql] Convert timestamp to date in Oracle SQL

How can we convert timestamp to date?

The table has a field, start_ts which is of the timestamp format:

'05/13/2016 4:58:11.123456 PM'

I need to query the table and find the maximum and min timestamp in the table but I'm not able to.

Select max(start_ts) 
from db 
where cast(start_ts as date) = '13-may-2016'

But the query is not returning any values.

Please help me in finding the max timestamp for a date.

You can try the simple one

select to_date('2020-07-08T15:30:42Z','yyyy-mm-dd"T"hh24:mi:ss"Z"') from dual;

Format like this while selecting:

to_char(systimestamp, 'DD-MON-YYYY')

Eg:

select to_char(systimestamp, 'DD-MON-YYYY') from dual;

If the datatype is timestamp then the visible format is irrelevant.

You should avoid converting the data to date or use of to_char. Instead compare the timestamp data to timestamp values using TO_TIMESTAMP()

WHERE start_ts >= TO_TIMESTAMP('2016-05-13', 'YYYY-MM-DD')
   AND start_ts < TO_TIMESTAMP('2016-05-14', 'YYYY-MM-DD')

You can use:

select to_date(to_char(date_field,'dd/mm/yyyy')) from table

Try using TRUNC and TO_DATE instead

WHERE
    TRUNC(start_ts) = TO_DATE('2016-05-13', 'YYYY-MM-DD')

Alternatively, you can use >= and < instead to avoid use of function in the start_ts column:

WHERE
   start_ts >= TO_DATE('2016-05-13', 'YYYY-MM-DD')
   AND start_ts < TO_DATE('2016-05-14', 'YYYY-MM-DD')

I'd go with the following:

Select max(start_ts) 
from db 
where trunc(start_ts) =  date'13-may-2016'

If you have milliseconds in the date string, you can use the following.

select TO_TIMESTAMP(SUBSTR('2020-09-10T09:37:28.378-07:00',1,23), 'YYYY-MM-DD"T"HH24:MI:SS:FF3')From Dual;

And then convert it to Date with:

select trunc(TO_TIMESTAMP(SUBSTR('2020-09-10T09:37:28.378-07:00',1,23), 'YYYY-MM-DD"T"HH24:MI:SS:FF3')) From Dual;

It worked for me, hope it will help you as well.

This may not be the correct way to do it. But I have solved the problem using substring function.

Select max(start_ts), min(start_ts)from db where SUBSTR(start_ts, 0,9) ='13-may-2016'

using this I was able to retrieve the max and min timestamp.