CODEWITHSUNDEEP
sqloracle-database
Alejandro Flores
Alejandro Flores

Reputation: 95

Result empty when date variable

I'm using an Oracle 10g Database. I have a table with a lot of information. One of the columns has a type of DATE declared as follows DATE_INSERT DATE. I'm trying to make a query and have it filtered by a specific Date. When I use the function TO_CHAR() in the where clause I get the information as expected

SELECT * FROM TABLE WHERE TO_CHAR(DATE_INSERT, 'DD/MM/YYYY') = '05/10/2018'

But when I use a DATE variable or the TO_DATE() function I get an empty result set.

SELECT * FROM TABLE WHERE DATE_INSERT = TO_DATE('05/10/2018','DD/MM/YYYY')

OR

SELECT * FROM TABLE WHERE DATE_INSERT = date '2018-10-05';

OR (date_var defined as a date previously)

SELECT * FROM TABLE WHERE DATE_INSERT = date_var;

By a DB department requirement I need to get rid of all functions TO_DATE() / TO_CHAR(). Can you help me know why is the filtering not working when DATE types are used in the query?

Upvotes: 0

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

This is due to the time component. I would recommend either:

WHERE DATE_INSERT >= date '2018-10-05' AND
      DATE_INSERT < date '2018-10-06'

Or:

WHERE TRUNC(DATE_INSERT) = date '2018-10-05'

Oracle supports function-based indexes. If you want an index to be used, you can create an index to support either of these queries.

Upvotes: 2

The Impaler
The Impaler

Reputation: 48800

In Oracle, a DATE column always stores the date and the time information. So the query you mention:

SELECT * FROM TABLE WHERE TO_CHAR(DATE_INSERT, 'DD/MM/YYYY') = '05/10/2018'

doesn't use equality, but queries for a range. Which range? The whole day, that is 24 hours.

For the other query to you'll need to use a range, as in:

SELECT * FROM TABLE 
  WHERE DATE_INSERT >= TO_DATE('05/10/2018','DD/MM/YYYY')
    AND DATE_INSERT < TO_DATE('06/10/2018','DD/MM/YYYY')

More clearly, to show the date & time info:

SELECT * FROM TABLE 
  WHERE DATE_INSERT >= TO_DATE('05/10/2018 00:00:00','DD/MM/YYYY HH24:MI:SS')
    AND DATE_INSERT < TO_DATE('06/10/2018 00:00:00','DD/MM/YYYY HH24:MI:SS')

Upvotes: 0

Related Questions