TIMESTAMP To DATE in Oracle SQL

Question

I have a column called Create_Date which has data in the format like 19-JUN-18 10.27.00.000000000 PM and data type is TIMESTAMP(6).

I am trying to look at date range like yesterday's date or between two dates in Create_Date without using TO_DATE(TO_CHAR(P.CREATE_DATE_TIME,'dd/mon/yy')) and entering the value as '19-JUN-18'.

I want to use Create_Date=SYSDATE-1 OR Create_Date=CURRENT_DATE-1 instead to filter on yesterdays date. Or Use Create_Date>=SYSDATE or Create_Date>=CURRENT_DATE to look at dates greater than or equal to today.

Can someone help?

Answer 1

You could use TRUNC:

SELECT *
FROM tab
WHERE Create_Date >= TRUNC(SYSDATE,'DD')  -- -1

-- or between to dates (using date literals)
WHERE Create_Date >= DATE 'yyyy-mm-dd'
   AND Create_Date < DATE 'yyyy-mm-dd'