Perform MySQL SELECT on dates inserted into the table as VARCHAR values

Let us first create a table −

mysql> create table DemoTable
(
   DueDate varchar(100)
);
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('21/10/2018');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values('18/08/2019');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('01/12/2012');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('31/01/2016');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+------------+
| DueDate    |
+------------+
| 21/10/2018 |
| 18/08/2019 |
| 01/12/2012 |
| 31/01/2016 |
+------------+
4 rows in set (0.00 sec)

Following is the query to perform SELECT on dates included in the above table as VARCHAR. Here, we are fetching all the date records after the date 2010-01-31 −

mysql> select *from DemoTable where str_to_date(DueDate,'%d/%m/%Y') > '2010-01-31';

This will produce the following output −

+------------+
| DueDate    |
+------------+
| 21/10/2018 |
| 18/08/2019 |
| 01/12/2012 |
| 31/01/2016 |
+------------+
4 rows in set (0.00 sec)

AmitDiwan

Published on 26-Sep-2019 07:48:16

• Related Questions & Answers
• Searching BETWEEN dates stored as varchar in MySQL?
• Perform MySQL SELECT on fields containing null values?
• MySQL INSERT INTO SELECT resulting in multiple rows inserted at once from another table
• Perform MySQL SELECT INTO user-defined variable
• MySQL INSERT INTO SELECT into a table with AUTO_INCREMENT
• MySQL select distinct dates from datetime column in a table?
• Perform MySQL search between two dates
• Delete last value and fix two new values (VARCHAR Numbers) in MySQL declared as VARCHAR?
• Split a string and insert it as individual values into a MySQL table?
• Find average on the basis of corresponding duplicate VARCHAR values in MySQL
• How can I convert the epoch stored in MySQL table into readable dates?
• How to select only non - numeric values from varchar column in MySQL?
• Perform multiple inserts with INSERT INTO SELECT and UNION in MySQL
• Select the table name as a column in a UNION select query with MySQL?
• How to apply EXTRACT() function on the dates stored in MySQL table?