STR_TO_DATE作为列，但没有找到列

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'

3 个解决方案

#1

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:

不能在where子句中使用列别名。MySQL有一个扩展，您可以在拥有子句中这样做(不需要进行任何聚合)。所以你能做什么:

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;

#2

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

不能使用date_format，因为它只是给定的名称，而是STR_TO_DATE(开始，'%d/%m/%Y')

#3

try this way

试试这个方法

date_format is not field so use start instead of date_format

date_format不是字段，所以使用start而不是date_format

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

#1