CODEWITHSUNDEEP
sqloracle-databasesql-like
Abhishek
Abhishek

Reputation: 878

Sql Query using 'Like' is giving results but using '=' does not returns any result in Oracle

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:

Upvotes: 0

Views: 584

Answers (3)

user330315
user330315

Reputation:

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')

Upvotes: 1

Ben
Ben

Reputation: 52863

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'

Upvotes: 1

Manam
Manam

Reputation: 355

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

Upvotes: 0

Related Questions