MySQL: How to ask for a date greater than today when a date is in a string

Question

Hi, I'm try to extract info from a MySQL DB into a MS SQL DB. The DB is a mess and the developer is no longer available.

All dates are in char fields, and I use

SELECT concat( mid(DueDate, 7, 4), mid(DueDate, 4, 2), mid(DueDate, 1, 2)) as DueDate FROM TableName

to get the date field in a format so MS sql server can import them.

Now, I want to export only the record with the date greater than today, so the questions are:

• What is the equivalent of GetDate() in MySQL?
• Is there a better way to cast the date to make the comparison?

Answer 1

In MySQL you can convert a string to a date using the STR_TO_DATE function.

An example usage is:

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
        -> '2004-04-31'

To get the current date in MySQL simply use the NOW() function. You can then check if a parsed date is later than today using something like

WHERE STR_TO_DATE('04/31/2009', '%m/%d/%Y') > NOW()

Answer 2

MySQL equivalent to getdate() is CURDATE()

mysql> select IF(CURDATE()<NOW(), 1, 0);
+---------------------------+
| IF(CURDATE()<NOW(), 1, 0) |
+---------------------------+
|                         1 | 
+---------------------------+
1 row in set (0.00 sec)

mysql> select IF(CURDATE()=NOW(), 1, 0);                                                                            +---------------------------+
| IF(CURDATE()=NOW(), 1, 0) |
+---------------------------+
|                         0 | 
+---------------------------+
1 row in set (0.00 sec)