There are records in table for particular date. But when I query with that value, I am unable to filter the records.

select * from TBL_IPCOLO_BILLING_MST
where LAST_UPDATED_DATE = '03-09-21';

The dates are in dd-mm-yy format.

CodePudding user response：

Your column might contain hours and seconds, but they can be hidden. So when you filter on the date, oracle implicitly adds time to the date. So basically you are filtering on '03-09-21 00:00:00'

Try to trunc your column:

select * from TBL_IPCOLO_BILLING_MST
where trunc(LAST_UPDATED_DATE) = '03-09-21';

Hope, I understood your question correctly. Oracle docs

CodePudding user response：

To the answer by Valeriia Sharak, I would just add a few things since your question is tagged Oracle. I was going to add this as a comment to her answer, but it's too long.

First, it is bad practice to compare dates to strings. Your query, for example, would not even execute for me -- it would end with ORA-01843: not a valid month. That is because Oracle must do an implicit type conversion to convert your string "03-09-21" to a date and it uses the current NLS_DATE_FORMAT setting to do that (which in my system happens to be DD-MON-YYYY).

Second, as was pointed out, your comparison is probably not matching rows due LAST_UPDATED_DATE having hours, minutes, and seconds. But a more performant solution for that might be:

...
WHERE last_update_date >= TO_DATE('03-09-21','DD-MM-YY') 
AND   last_update_date <  TO_DATE('04-09-21','DD-MM-YY')

This makes the comparison without wrapping last_update_date in a TRUNC() function. This could perform better in either of the following circumstances:

1. If there is an index on last_update_date that would be useful in your query
2. If the table with last_update_date is large and is being joined to other tables (because it makes it easier for Oracle to estimate the number of rows from your table that are inputs to the join).