mysql - 如何将 varchar 转换/转换为日期？

Question

我在 mySQL 5.1 中有一个数据类型为 varchar(mm-dd-yyyy) 的日期列。如何将其转换为 DATE？

这是我目前所拥有的 -

SELECT id, date 
FROM tableName 
WHERE (CAST((SUBSTRING (date FROM 7 FOR 4 )||'/'||SUBSTRING (date FROM 4 FOR 2 )||'/'||SUBSTRING (date FROM 1 FOR 2 )) AS DATE) >= '01/01/2012' ) 
ORDER BY date DESC;

得到这个

error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM 7 FOR 4 )

请帮忙。

Answer 1

您可以使用 MySQL 的 STR_TO_DATE()功能

SELECT id, date 
FROM tableName 
WHERE STR_TO_DATE(date,'%Y-%m-%d') >= '01/01/2012' 
ORDER BY date DESC;

尽管我怀疑您使用 Unix 时间戳会更轻松

SELECT id, date 
FROM tableName 
WHERE UNIX_TIMESTAMP(STR_TO_DATE(date,'%d/%m/%Y')) >= UNIX_TIMESTAMP('01/01/2012') 
ORDER BY date DESC;