0

The Query using LIKE :(This query when fired gives the desired result)

select * from catissue_audit_event where event_timestamp like '16-DEC-14'

But when using query with '=' results in an empty resultset

select * from catissue_audit_event where event_timestamp='16-DEC-14'

Here event_timestamp is of type Date

Strange thing is that the query runs for other dates such as:

select * from catissue_audit_event where event_timestamp='15-DEC-14'

What can be the issue? I already checked for leading and trailing spaces in the data

Output after running the first query:

Output after running the first query:

Abhishek
  • 878
  • 12
  • 28
  • could you share the sample output which you got from first query? – Panther Dec 17 '14 at 08:23
  • 1
    http://stackoverflow.com/questions/13337635/oracle-sql-select-date-with-timestamp – idstam Dec 17 '14 at 08:25
  • Have you tried to use TO_DATE to convert timestamp to date? – kiks73 Dec 17 '14 at 08:26
  • i am getting the data for '15-DEC-14' using the same query but getting trouble while retrieving results for '16-DEC-14' – Abhishek Dec 17 '14 at 08:27
  • @Panter please look at the edit – Abhishek Dec 17 '14 at 08:34
  • Don't use `LIKE` on `DATE` or `TIMESTAMP` columns. Your query is subject to implicit data type conversion and does not do what you think it's doing. What exactly are you trying to do? –  Dec 17 '14 at 08:42
  • @a_horse_with_no_name yes i want to use '=' only but it's not giving me results, so tried using 'LIKE' and it is producing the output – Abhishek Dec 17 '14 at 08:43

3 Answers3

1

The reason why this is different is different to the solution to your issue.

The solution to your issue is to stop performing date comparisons by implicit conversion to a string. Convert your string to a date to perform a date comparison:

select * from catissue_audit_event where event_timestamp = date '2014-12-16'

I cannot stress this enough; when performing a date comparison only compare dates.

Your column EVENT_TIMESTAMP is being implicitly (this is bad) converted to a date in accordance with your NLS_DATE_FORMAT, which you can find as follows:

select * from nls_session_parameters

This governs how date-data is displayed and implicitly converted. The reason why LIKE works and and = doesn't is because your NLS_DATE_FORMAT is masking additional data. In other words, your date has a time component.

If you run the following and then re-select the data from your table you'll see the additional time component

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'

Thus, if you want all the data for a specific date without constraint on time you'll need to remove the time component:

select * from catissue_audit_event where trunc(event_timestamp) = date '2014-12-16'
Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
  • got your point , but why am i getting results when i pass some other date like '15-DEC-14' in query using '=' – Abhishek Dec 17 '14 at 08:55
1

In Oracle a DATE (and of course a TIMESTAMP) column contains a time part as well.

Just because your SQL client is hiding the time, doesn't mean it isn't there.

If you want all rows from a specific day (ignoring the time) you need to use trunc() 

select * 
from catissue_audit_event 
where trunc(event_timestamp) = DATE '2014-12-16';

Be aware that this query will not use an index on the event_timestamp column.

You should also not rely on implicit data type conversion as you do with the expression event_timestamp = '16-DEC-14. That statement is going to fail if I run it from my computer because of different NLS settings. Always use a proper DATE literal (as I have done in my statement). If you don't like the unambiguous ISO date, then use to_date(): 

where trunc(event_timestamp) = to_date('16-12-2014', 'dd-mm-yyyy');

You should avoid using month names unless you know that all environments (which includes computers and SQL clients) where your SQL statement is executed are using the same NLS settings. If you are sure, you can use e.g. to_date('16-DEC-14', 'dd-mon-yy')

  • but why am i getting results when i pass some other date like '15-DEC-14' in query using '=' – Abhishek Dec 17 '14 at 09:34
  • @Abhishek: maybe you do have some `date` values that have the time `00:00:00` on that day. You need to change the *dispaly* format of dates (and timestamps) in your SQL client in order to see that. –  Dec 17 '14 at 09:40
0

have you tried matching the event_timestamp format example: DD-MMM-YY with the date that you are passing?

Manam
  • 354
  • 5
  • 16
  • not facing any issues while retrieving other entries – Abhishek Dec 17 '14 at 08:36
  • 2
    There is no such thing as a date "format" (and would be `DD-MON-YY` if there was one) – Ben Dec 17 '14 at 08:50
  • @Ben here is how you format your SQL query, u can start with Select * from tablename where FORMAT(event_timestamp,'DD-MMM-YY') = '15-DEC-14' and he says SQL Query not Oracle, and if its oracle you can use the to_char method like this: TO_CHAR(vent_timestamp, 'DD-MMM-YY') but Abhishek I don't know why it is not working for you. – Manam Dec 17 '14 at 08:53