I have following table

And I want to select records have Insert_Date equal Now date without write

select * from *My_Table* where insert_date = '11-JAN-23' ;

I tried

select * from *My_Table* where insert_date = TRUNC(CURRENT_DATE) ;

But it doesn't work;

CodePudding user response：

In Oracle, a DATE is a binary data-type that ALWAYS has the components year, month, day, hour, minute and second. However, client applications (SQL*Plus, SQL Developer, etc.) often do not display the entire DATE and only display the date component and not the time component; that does not mean that the time component does not exist, only that you aren't seeing it with the default formatting.

This means that your date probably also has a non-midnight time component and your query is not matching on the time components. To solve it, you can select on a range:

SELECT *
FROM   My_Table
WHERE  insert_date >= TRUNC(CURRENT_DATE)
AND    insert_date <  TRUNC(CURRENT_DATE)   INTERVAL '1' DAY;

Or you can use TRUNC, but that would prevent you using an index on the insert_date column:

SELECT *
FROM   My_Table
WHERE  TRUNC(insert_date) = TRUNC(CURRENT_DATE);

Note: To change how SQL*Plus and SQL Developer format dates in your current session, you can use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

CodePudding user response：

Try:

SELECT * FROM My_Table WHERE Insert_Date = CAST( GET_DATE() AS Date)