3

Whats wrong in this query:

select *, STR_TO_DATE(start, '%d/%m/%Y') as date_format from dates where date_format >= 2018-03-19

error:

Column not found: 1054 Unknown column 'date_format' in 'where clause'
natas
  • 456
  • 2
  • 9
  • 15
  • Possible duplicate of [Using column alias in WHERE clause of MySQL query produces an error](https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) – Sebastian Brosch Mar 19 '18 at 10:44
  • Possible duplicate of [Referring to a Column Alias in a WHERE Clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) – Amin Negm-Awad Mar 19 '18 at 10:44

3 Answers3

3

You cannot use a column alias in a where clause. MySQL has an extension where you can do so in a having clause (without doing any aggregation). So you can do:

select d.*, STR_TO_DATE(start, '%d/%m/%Y') as date_format
from dates d
having date_format >= '2018-03-19';

The normal advice is to repeat the expression:

select d.*, STR_TO_DATE(start, '%d/%m/%Y') as date_format
from dates d
having STR_TO_DATE(start, '%d/%m/%Y') >= '2018-03-19';

However, I would strongly recommend that you change the structure of the table. The date should not be stored as a string. You can easily fix this:

update dates
    set start = STR_TO_DATE(start, '%d/%m/%Y');

alter table dates modify column start date;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You can not use date_format as it is just given name but STR_TO_DATE(start, '%d/%m/%Y')

Mr.No
  • 83
  • 9
0

try this way

date_format is not field so use start instead of date_format 

select *, STR_TO_DATE(start, '%d/%m/%Y') as date_format from dates where start >= 2018-03-19
Bhargav Chudasama
  • 6,928
  • 5
  • 21
  • 39