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?

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

Solution 2

As it's a timestamp I'd cast the truncated (to midnight) current date to a timestamp for clarity; Oracle will use an index on that column even if you leave it as a date, but it doesn't hurt to make it explicit:

where create_date >= cast(trunc(sysdate) as timestamp)

The trunc() function defaults to truncating to midnight; you can explicitly include 'DD' as a second argument if you prefer (for even more clarity, though some would see it as noise).

If you want a range, say yesterday:

where create_date >= cast(trunc(sysdate) - 1 as timestamp)
and create_date < cast(trunc(sysdate) as timestamp)

If you want to specify other dates then you can use timestamp literals, e.g. to see everything for May:

where create_date >= timestamp '2018-05-01 00:00:00'
and create_date < timestamp '2018-06-01 00:00:00'

Comments

[Alex Poole]

Dates and timestamps don't have any intrinsic format; you're just seeing them however your client decides to format them, often based on your NLS settings. I'd also avoid using YY in a format model, or relying on implicit conversions.