create table demo_tab(id number(2),name varchar2(50),from_date date);
Table created.
insert into demo_tab values(2,'AUDI','04-MAR-2019');
1 row created.
insert into demo_tab values(3,'BMW','06-MAR-2019');
1 row created.
insert into demo_tab values(4,'DODGE','07-MAR-2019');
1 row created.
insert into demo_tab values(5,'RAM','08-MAR-2019');
1 row created.
insert into demo_tab values(6,'FORD','09-MAR-2019');
1 row created.
COMMIT;
Commit complete.
SELECT * FROM DEMO_TAB;
ID NAME FROM_DATE
---------- -------------------------------------------------- ---------
2 AUDI 04-MAR-19
3 BMW 06-MAR-19
4 DODGE 07-MAR-19
5 RAM 08-MAR-19
6 FORD 09-MAR-19
FILTERING DATA WITH DATE COLUMN
SELECT * FROM DEMO_TAB WHERE FROM_DATE >='01-JAN-2019' AND FROM_DATE <='28-FEB-2019';
no rows selected
AS EXPECTED NO RECS , BUT WHEN I TRY THE FOLLOWING
SELECT * FROM DEMO_TAB
WHERE TO_CHAR(FROM_DATE,'DD-MM-YYYY') >='01-01-2019' AND TO_CHAR(FROM_DATE,'DD-MM-YYYY')<='28-02-2019';
ID NAME FROM_DATE
---------- -------------------------------------------------- ---------
2 AUDI 04-MAR-19
3 BMW 06-MAR-19
4 DODGE 07-MAR-19
5 RAM 08-MAR-19
6 FORD 09-MAR-19
THERE IS NO DATA FOR THE SPECIFIED DATE BUT IT RETURNS ALL THE RECORDS FROM THE TABLE..
So please share your ideas on this..Is there anything with the configuration of database??
PRO-TIP:
The string:
04-MAR-19
IS NOT A DATE!
To make it a date, you need to TO_DATE it. If you insert the string into a DATE column WITHOUT to_date'ing it, the database will use implicit conversions. Which can lead to... unexpected effects.
And when you do:
SELECT * FROM DEMO_TAB
WHERE TO_CHAR(FROM_DATE,'DD-MM-YYYY') >='01-01-2019' AND TO_CHAR(FROM_DATE,'DD-MM-YYYY')<='28-02-2019';
You're converting the dates strings. Which means you have character-based comparisons. i.e. the database is asking:
Is the text value 04-03-2019 between the text values 01-01-2019 & 28-02-2019.
So it compares character by character:
Is 0 (zero) between 0 (zero) and 2 (two)
YES!
So you get the row.
To do this right, either to_date the strings:
SELECT count(*) FROM DEMO_TAB
WHERE FROM_DATE BETWEEN to_date ( '01-01-2019', 'DD/MM/YYYY' )
AND to_date ( '28-02-2019', 'DD/MM/YYYY' );
COUNT(*)
0 Or make them date literals:
SELECT count(*) FROM DEMO_TAB
WHERE FROM_DATE BETWEEN DATE'2019-01-01'
AND DATE'2019-02-28';
COUNT(*)
0PS - STOP SHOUTING!