MySQL STR_TO_DATE函数返回NULL

Can anyone please help me to find out solution - I am running following query

任何人都可以帮我找出解决方案 - 我正在运行以下查询

SELECT STR_TO_DATE(order_date, '%d-%m-%Y') FROM `order_master`

It is returning NULL however I have 2 rows in table with values as 'Today' and '15-08-2017' , I want to compare varchar fields with date, tried a lot but could not succeed. Thank you.

它返回NULL但是我在表中有2行,其值为'Today'和'15 -08-2017',我想将varchar字段与日期进行比较,尝试了很多但是没有成功。谢谢。

1 个解决方案

#1

SELECT STR_TO_DATE('15-08-2017', '%d-%m-%Y')

works just fine. It yields the DATE value 2017-08-15.

工作得很好。它产生DATE值2017-08-15。

SELECT STR_TO_DATE('Today', '%d-%m-%Y')

obviously doesn't. It yields NULL.

显然没有。它产生NULL。

You could use something like this to put the present date in place of the STR_TO_DATE() result if it came back NULL.

如果它返回NULL,你可以使用这样的东西来放置当前日期代替STR_TO_DATE()结果。

SELECT IFNULL(STR_TO_DATE('15-08-2017', '%d-%m-%Y'), CURDATE())

But your best bet is to use something like this

但你最好的选择是使用这样的东西

SELECT IF('Today'=order_date, CURDATE(), STR_TO_DATE(order_date ,'%d-%m-%Y'));

This will substitute the current date for an order_date value of Today, but leave in place the NULL values for other malformed order_date values.

这将替换当前日期的order_date值为Today,但保留其他格式错误的order_date值的NULL值。

#1

SELECT STR_TO_DATE('15-08-2017', '%d-%m-%Y')