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;