0

I'm on an Oracle database, the following query:

select codi_belf, data_elab,id_inte,anno from mimsspam_tab where id_inte = 504;

retrieves 6 records 

E924    05-AGO-15   504 2015
D458    05-AGO-15   504 2015
D458    05-AGO-15   504 2015
B275    05-AGO-15   504 2015
E924    05-AGO-15   504 2015
B275    05-AGO-15   504 2015

where "data_elab" is a field of type DATE and AGO is just the Italian locale for AUG.

I try to filter by date like this: 

select codi_belf, data_elab,id_inte,anno 
from mimsspam_tab where id_inte = 504 
and data_elab = TO_DATE('05/08/2015','dd/MM/yyyy');

And I get NO results..

Moreover if I filter this way:

select codi_belf, data_elab,id_inte,anno  
from mimsspam_tab where id_inte = 504 
and TO_CHAR(data_elab,'dd/MM/yyyy')='05/08/2015';

I got my six records back!

Shouldn't the two ways of filtering be equivalent? Why they aren't? And why the first eats all my six records? Could this depend on a weird configuration of the oracle installation I'm querying to?

Tito Zoe Chiacchiera
  • 5
  • 2
  • 2
    In your selects use a `to_char` on that date with a format that includes hours & minutes. – Mat Aug 05 '15 at 11:38
  • Possible duplicate of [this](http://stackoverflow.com/q/6749369/266304) or [this](http://stackoverflow.com/q/19216076/266304) or many others. – Alex Poole Aug 05 '15 at 12:35

2 Answers2

3

Sounds like your data_elab column has time elements that your default nls_date_format setting is causing not to be shown when you select that column.

If there's no index on data_elab, then the following should return you the rows:

and trunc(data_elab) = TO_DATE('05/08/2015','dd/MM/yyyy');

If there's an index on data_elab, and you want it to be used, then the following should work:

and data_elab >= TO_DATE('05/08/2015','dd/MM/yyyy')
and data_elab < TO_DATE('06/08/2015','dd/MM/yyyy')
Boneist
  • 22,910
  • 1
  • 25
  • 40
2

What you see in your first query is your database default date format, determined by your NLS parameters. Your date field actually contains time information, you just don't see it.

In your second query, when you use to_date(), you convert a string to a date and don't provide time information, so you get 00:00:00 for the time part of your date. Comparing it to your field (which includes time information) would result in no match.

In your third query, when you use to_char(), you convert your date field into a string that doesn't include time information. Comparing it to your literal string, you get matched records.

Try to think of a date field as a binary information, not a character string. Try not rely on database default parameters.

Erkan Haspulat
  • 12,032
  • 6
  • 39
  • 45
  • Both @Boneist and you are right. If I select a to_char(data_elab,'dd/mm/yyyy hh:mm:ss') from the table I can see time information e.g 05/08/2015 11:08:31 – Tito Zoe Chiacchiera Aug 05 '15 at 13:56