Mysql – where clause return empty row in view thesql

MySQLunionview

i have two tables with the schema below: 

INCOME TABLE:
INCOME_ID, INCOME_NAME, INCOME_DATE

OUTCOME TABLE:
OUTCOME_ID, OUTCOME_NAME, OUTCOME_DATE

EXAMPLE DATA INCOME
INCOME_ID | INCOME_NAME | INCOME_DATE
1         | FIRST EXMPL | 24-07-2016
2         | SEC EXAMPLE | 25-07-2016
ETC
EXAMPLE DATA OUTCOME
OUTCOME_ID | OUTCOME_NAME | OUTCOME_DATE
1          | FIRST        | 24-07-2016
2          | SEC          | 03-08-2016
ETC

so i use union all to combine these table

SELECT INCOME_DATE AS DATE, INCOME_NAME AS NAME FROM INCOME
UNION ALL
SELECT OUTCOME_DATE, OUTCOME NAME FROM OUTCOME

and i use query above to create view

CREATE VIEW V_BALANCE AS SELECT INCOME_DATE AS DATE, INCOME_NAME AS NAME
UNION ALL
SELECT OUTCOME_DATE, OUTCOME_NAME FROM OUTCOME

the view was succesfully created but when i want to add where clause in view, it returns empty row

SELECT * FROM V_BALANCE WHERE DATE BETWEEN = '24-07-2016' AND '03-08-2016'

Best Answer

Assuming that the two date columns are indeed DATE columns, the problem is that you are not using proper format in the date literals. Use ANSI format:

SELECT * 
FROM V_BALANCE 
WHERE DATE BETWEEN '2016-07-24' 
               AND '2016-08-03' ;

See the documentation for other options: Date and time literals.

If the two date columns are not DATE but some CHAR or VARCHAR, then the issue is there. Convert the columns to the proper date type, otherwise the comparisons will work incorrectly.

Related Question