MySQL: how to set date greater than today when date is in string
I am trying to pull information from MySQL database to MS SQL database. The DB is a mess and the developer is no longer available.
All dates are in char fields and I am using
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 that MS SQL Server can import them.
Now I only want to export a record with a date that is greater than today, so the questions are:
- What is the equivalent of GetDate () in MySQL?
- Is there a better way to make the date compare?
+1
source to share
2 answers
In MySQL, you can convert a string to a date using the STR_TO_DATE function .
Usage example:
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'
To get the current date in MySQL, just use the NOW () function. Then you can check if the syntax date has passed later than today using something like
WHERE STR_TO_DATE('04/31/2009', '%m/%d/%Y') > NOW()
+11
source to share
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)
+1
source to share