Searching BETWEEN dates stored as varchar in MySQL?

MySQLMySQLi Database

You can search between dates stored as varchar using STR_TO_DATE(). The syntax is as follows −

select *from yourTableName where STR_TO_DATE(LEFT(yourColumnName,LOCATE('',yourColumnName)),'%m/%d/%Y') BETWEEN 'yourDateValue1' AND 'yourDateValue2’;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table SearchDateAsVarchar
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> ShippingDate varchar(100),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.99 sec)

Insert some records in the table using INSERT command. The query is as follows −

mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/28/2011 9:58 AM');
Query OK, 1 row affected (0.19 sec)
mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/18/2011 10:50:39 AM');
Query OK, 1 row affected (0.55 sec)
mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/22/2011 11:45:40 AM');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from SearchDateAsVarchar;

The following is the output −

+----+-----------------------+
| Id | ShippingDate          |
+----+-----------------------+
|  1 | 6/28/2011 9:58 AM     |
|  2 | 6/18/2011 10:50:39 AM |
|  3 | 6/22/2011 11:45:40 AM |
+----+-----------------------+
3 rows in set (0.00 sec)

Here is the query to search between dates stored as varchar −

mysql> select *from SearchDateAsVarchar where
STR_TO_DATE(LEFT(ShippingDate,LOCATE(' ',ShippingDate)),'%m/%d/%Y') BETWEEN
'2011-06-20' AND '2011-06-28';

The following is the output −

+----+-----------------------+
| Id | ShippingDate          |
+----+-----------------------+
|  1 | 6/28/2011 9:58 AM     |
|  3 | 6/22/2011 11:45:40 AM |
+----+-----------------------+
2 rows in set (0.00 sec)
raja
Published on 27-Feb-2019 11:31:19
Advertisements