Convert timestamp to date in Oracle SQL

Question

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.

Answer 1

CAST(timestamp_expression AS DATE) 

For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;

Answer 2

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')