The query below is being used to get a count of records between some days. Actually, there are no records for that particular date so query should return 0. But due to some mistake, it returns the value incorrectly.
SELECT COUNT(*)
FROM transaction_tb
WHERE STATUS NOT IN ('Wrong', 'Dont')
AND to_char(date, 'DD-MM-YYYY') BETWEEN '01-04-2019' AND '31-03-2020';
But when trying with the below query, it returned 0 as excepted.
SELECT COUNT(*)
FROM transaction_tb
WHERE STATUS NOT IN ('Wrong', 'Dont')
AND to_char(date, 'YYYYMMDD') BETWEEN '20190401' AND '20200331';
Both queries should return the same value.
What is the difference between these queries?
If you compare two dates, you get date comparison semantics. If you compare two strings, you get string comparison semantics which is alphabetical order. The string "10-10-1950" comes alphabetically between the string "01-04-2019" and the string "31-03-2020". Despite the fact that the date it represents is obviously much earlier than either of the dates represented by the other strings.
If you compare dates to dates, you get date comparison semantics which is what you want. Either use date literals
where date_column between date '2019-01-04' and date '2020-03-31'
or use a to_date to convert your strings to dates
where date_column between to_date( '01-04-2019', 'MM-DD-YYYY' )
AND to_date( '31-03-2020', 'MM-DD-YYYY' )