0

I'm facing a strange probleme with my Oracle SQL Query, my query looks like :

select * 
from user e 
where  e.year= 2014 and e.user =305 and e.usercat =22 and e.datereg= '25/07/2014'

When a select the whole content, I can find this specific user, but once I add the column datereg to the query, it doesn't give me any result (0 row)!!

I have a problem with the value of the datereg column only for this user, and no probleme with other users (when i change the datereg value).

Any idea please ?

Barmar
  • 741,623
  • 53
  • 500
  • 612
MDIT
  • 1,508
  • 5
  • 25
  • 38
  • Can you show the records from the table using `select * from user e where e.year= 2014 and e.user =305 and e.usercat =22`? Is the `datereq` column a date? – Jens Jul 31 '14 at 11:46
  • Yes - what data type is the column `datereq`? This is a critical piece of information. – Nick.Mc Jul 31 '14 at 11:47
  • @ElectricLlama it's a "Date" – MDIT Jul 31 '14 at 11:50
  • 1
    `e.datereg= '25/07/2014'` compares a `date` with a string literal and thus implicit data type conversion takes place. Never ever rely on implicit data type conversion! Use a proper date literal instead (e.g. ANSI date literals or by using `to_date()`) –  Jul 31 '14 at 12:00
  • @a_horse_with_no_name in my case, do you think "to_date" is better for and valid for the whole column (even if i dont have any problem with other values?) – MDIT Jul 31 '14 at 12:12
  • 1
    Always use `to_date()` when specifying date constants with a format mask that is independent of the language, so no month *names* in there. You should also read Gordon's answer about the time part of an Oracle `date` column –  Jul 31 '14 at 12:15

2 Answers2

3

I assume datereg is of type date.

So the criteria should be: 

e.datereg = to_date('25/07/2014','DD/MM/YYYY')

if datereg also has a time portion try 

trunc(e.datereg) = to_date('25/07/2014','DD/MM/YYYY')

which brings me to the same solution as Gordon :)

Conffusion
  • 4,335
  • 2
  • 16
  • 28
  • but why it works with other values like '26/07/2014' !! and why I have to use to_date function ? – MDIT Jul 31 '14 at 11:53
  • if it works with with 26 and not with 25 then there is something else going on. I've seen to many unpredicted results when letting the system detect a date format. So I like to force things a little bit. – Conffusion Jul 31 '14 at 12:04
  • Maybe because i use "sysdate" function to insert this row ? – MDIT Jul 31 '14 at 12:25
  • This solution doesn't work for me !! – MDIT Jul 31 '14 at 14:21
2

This would typically happen when you have a time component. Try changing the logic to:

trunc(e.datereg) = '25/07/2014'

EDIT:

Your version fails because a date with a time is not equal to a date without a time. This happens all the time with comparisons using sysdate, which has a time component.

Use to_date() for the date constant if you need. I would always use ISO standard formats for this:

trunc(e.datereg) = to_date('2014-07-25', 'YYYY-MM-DD')

You can also use the default date format:

trunc(e.datereg) = '25-07-2014'

However, this can be influenced by internationalization settings.

EDIT II:

By default, I mean the "default" value of NLS_DATE_FORMAT. It is better to do the conversions explicitly. Oracle is well documented on the use of date constants. I think I now prefer:

trunc(e.datereg) = DATE '2014-07-25'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It works, but could you please give us a clear explanation of the problem ? Thanks – MDIT Jul 31 '14 at 11:49
  • I'm somewhat surprised this works without an explicit call to `to_date()` - is this a special sort of date literal that's guaranteed to work regardless of NLS settings? – Frank Schmitt Jul 31 '14 at 11:51
  • In fact, I just checked - this will fail with American NLS settings (ORA-01843: not a valid month) – Frank Schmitt Jul 31 '14 at 11:53
  • @FrankSchmitt then, what's the best solution? – MDIT Jul 31 '14 at 11:55
  • Use an explicit `to_date()` call (as suggested by Conffusion), and if your date contains a time component, add a `trunc()`, as suggested by Gordon Linoff. – Frank Schmitt Jul 31 '14 at 11:57
  • 1
    `trunc(e.datereg) = '25-07-2014'` is rather dangerous (because of the NLS settings you mentioned) - I would not include that in an answer - especially not in an answer to someone who apparently did not understand the difference between a "real" date value and a character literal. –  Jul 31 '14 at 12:03
  • @a_horse_with_no_name . . . I think the problem is the time component on the date. Oracle is rather confusing, because it often prints out dates without the time component, making it look like the comparisons are failing. – Gordon Linoff Jul 31 '14 at 12:04
  • Yes I know that, but relying on implicit data type conversion is still a bad habit (and one that is begging for problems) –  Jul 31 '14 at 12:06
  • Exactly, i used "sysdate" function. i think, it's better to usually use trunc function ? – MDIT Jul 31 '14 at 12:07
  • @MehdiBoussarhane . . . For your purposes, with this query, the `trunc()` function is a fine solution. – Gordon Linoff Jul 31 '14 at 12:08
  • Any particular reason this keeps getting downvotes? The OP has admitted that the time component was the problem. – Gordon Linoff Jul 31 '14 at 12:29
  • There is no default date format in Oracle, though you can use a date literal and you're suggesting that the OP use implicit conversion, which will be wrong for the vast majority of people who use this answer in the future, and as you don't know the OPs NLS_DATE_FORMAT potentially for the OP as well. – Ben Jul 31 '14 at 12:58
  • @Ben What's the solution in your opinion ? – MDIT Jul 31 '14 at 13:05
  • Gordon's given you the solution @Mehdi, there's just extra stuff that could change on a session-by-session basis. Convert explicitly to a date and you'll be fine. – Ben Jul 31 '14 at 13:14
  • @Ben The problem that I'm using this Query in Java application, I have to use the correct solution, because i will not have to change it everytime. This is why I have to choose the correct answer and the correct function. And i will use "trunc(e.datereg) = to_date('2014-07-25', 'YYYY-MM-DD') " is better i guess ? – MDIT Jul 31 '14 at 13:27
  • @MehdiBoussarhane . . . That is a very reasonable solution. – Gordon Linoff Jul 31 '14 at 16:57