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


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







All Articles