Output of the query in oracle

Question

Query 1 :

select count(*) from CI_TXN_HEADER where TXN_HEADER_DTTM = '25-JAN-13';

Result: 1

Query 2 :

select count(*) from CI_TXN_HEADER where TXN_HEADER_DTTM like '25-JAN-13';

Result: 19

In my DB I have 19 rows with TXN_HEADER_DTTM as 25-JAN-13. Data Type of TXN_HEADER_DTTM is DATE.

Can someone please explain the difference in output?

@AbsoluteƵERØ There are 19 rows with the given date in database, so the output should be same in both cases right? It should match 19 rows i suppose. — Curious Techie, Nov 03 '17 at 07:15
Can you run this query and post the output.? - `select count(*), TXN_HEADER_DTTM FROM CI_TXN_HEADER GROUP BY TXN_HEADER_DTTM;` — Kaushik Nayak, Nov 03 '17 at 07:21

score 4 · Accepted Answer · answered Nov 03 '17 at 07:19

An Oracle DATE column contains a date and a time. The LIKE condition is only for VARCHAR columns. If applied to other data types Oracle implicitly converts that to a varchar (using rules depending on the current client settings).

So you might have rows with e.g. 2013-01-25 17:42:01, however the string constant '25-JAN-13' is (most probably) converted to: 2013-01-25 00:00:00 and thus the = comparison doesn't work.

To find all rows for a specific day use trunc() and a proper date literal. Don't rely on the evil implicit data type conversion to specify date values.

Use trunc() to set the time part of a DATE value to 00:00:00:

I prefer ANSI SQL date literals:

select count(*) 
from CI_TXN_HEADER 
where trunc(TXN_HEADER_DTTM) = DATE '2013-01-25';

You can also use Oracle's to_date:

select count(*) 
from CI_TXN_HEADER 
where trunc(TXN_HEADER_DTTM) = to_date('2013-01-25', 'yyyy-mm-dd');

Note that Oracle can't use an index on TXN_HEADER_DTT, so if performance is critical use a range query:

select count(*) 
from CI_TXN_HEADER 
where TXN_HEADER_DTTM >= DATE '2013-01-25'
  and TXN_HEADER_DTTM < DATE '2013-01-25' + 1;

score 2 · Answer 2 · answered Nov 03 '17 at 07:20

The difference between like and equal is explained in this link very good https://stackoverflow.com/a/2336940/4506285

I checked your problem on my table and I get the same results.

This link helps also to understand how to compare dates in sql https://stackoverflow.com/a/18505739/4506285

score 1 · Answer 3 · answered Nov 03 '17 at 07:12

1

Maybe your data consists of space, it is not exactly '25-JAN-13' but ' 25-JAN-13';

Please refer this two link:

answered Nov 03 '17 at 07:12

Alden Ken

142
8

Output of the query in oracle

Query 1 :

Query 2 :

3 Answers3