How to convert/cast varchar to date?

mysql, mysql-error-1064, sql

Solution

You can use MySQL's `STR_TO_DATE()` function

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

Although I suspect you will have an easier time using Unix Timestamps

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

Problem

I have a date column with data type `varchar(mm-dd-yyyy)` in mySQL 5.1. How do I convert it to DATE? Here is what I have so far - ``` 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; ``` Getting this 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 ) Please help.

Original source