Convert timestamp to date in Oracle SQL

CAST(timestamp_expression AS DATE)

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


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

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

Format like this while selecting:

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

Eg:

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

Tags:

Sql

Oracle11G