Syntax error Comparison of varchar date records from the current date in MySQL

Comparison of varchar date records from the current date in MySQL

For date comparison, you can use STR_TO_DATE(). Following is the syntax −

select * from yourTableName
where str_to_date(yourColumnName,'yourFormatSpecifier') > curdate();

Let us first create a −

mysql> create table DemoTable1397
   -> (
   -> AdmissionDate varchar(40)
   -> );s
Query OK, 0 rows affected (0.97 sec)

Insert some records in the table using insert −

mysql> insert into DemoTable1397 values('01/04/2019');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1397 values('27/09/2019');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1397 values('29/09/2018');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1397 values('29/09/2019');
Query OK, 1 row affected (0.08 sec)

Display all records from the table using select −

mysql> select * from DemoTable1397;

This will produce the following output −

+---------------+
| AdmissionDate |
+---------------+
| 01/04/2019    |
| 27/09/2019    |
| 29/09/2018    |
| 29/09/2019    |
+---------------+
4 rows in set (0.00 sec)

Let us first find the current date −

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2019-09-27 |
+------------+
1 row in set (0.00 sec)

Following is the query to compare varchar date with the current date in MySQL −

mysql> select * from DemoTable1397
   -> where str_to_date(AdmissionDate,'%d/%m/%Y') > curdate();

This will produce the following output −

+---------------+
| AdmissionDate |
+---------------+
| 29/09/2019    |
+---------------+
1 row in set (0.07 sec)
Updated on: 2019-11-11T10:41:10+05:30

718 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements