My table EMPLOYEE contains loaddate column having string datatype.
In this column dates are stored like
'2019-12-8',
'2019-12-9',
'2019-12-10',
'2019-12-11',
'2019-12-12',
'2019-12-13',
'2019-12-14',
'2019-12-15',
when I am running below query
SELECT * FROM employee where loaddate between '2019-12-8' and '2019-12-14';
I am getting 0 records. But when I am running below query getting records but only from 2019-12-10 to 2019-12-14' though table contains records for 8 & 9
SELECT * FROM employee where loaddate between '2019-12-08' and '2019-12-14';
The difference between above two queries is 2019-12-8 and 2019-12-08. So don't understand why first query is not giving any record while second query is giving records but not all.
The answer lies in understanding of below parts.
1. Between operator functionality.
Refer : impala between operator
BETWEEN Operator : expression BETWEEN lower_bound AND upper_bound
a. In a WHERE clause, compares an expression to both a lower_bound and upper_bound.
b. The comparison is successful if the expression is greater than or equal to the lower_bound, and less than or equal to the upper_bound.
c. If the bound values are switched, so the lower_bound is greater than the upper_bound, does not match any values.
2. datatype used is string but between operator is typically used with numeric data types as per the documentation.
3. order by of the loaddate
Clearly the value '2019-12-8' is greater than '2019-12-15' in the ascending order. Insert two more values '2019-12-08' and '2019-12-09' they will come at the top when same order by query is run since they will be less than '2019-12-10'.
SELECT loaddate FROM employee order by loaddate;
'2019-12-10'
'2019-12-11'
'2019-12-12'
'2019-12-13'
'2019-12-14'
'2019-12-15'
'2019-12-8'
'2019-12-9'
SELECT * FROM employee where loaddate between '2019-12-8' and '2019-12-14'; ---zero results because lower_bound('2019-12-8') is the larger value than upper_bound('2019-12-14'). (case c)
SELECT * FROM employee where loaddate between '2019-12-08' and '2019-12-14'; ---getting records from 2019-12-10 to 2019-12-14 because 08 and 09 don't exist.
SELECT * FROM employee where loaddate between '2019-12-08' and '2019-12-9'; ---this will fetch all the data in the sample considering 10 is greater than 08(assuming 08 and 09 are not inserted yet).