How to return today's records using DATE from DATETIME Field?

MySQLMySQLi Database

Let us first create a table in which one of the column is of datetime type;

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ShippingDate datetime
);
Query OK, 0 rows affected (0.54 sec)

Insert some records in the table using insert command.

mysql> insert into DemoTable(ShippingDate) values('2019-03-01 05:45:32');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable(ShippingDate) values('2019-04-13 11:34:56');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(ShippingDate) values('2019-03-15 04:45:23');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(ShippingDate) values('2019-04-11 12:10:02');
Query OK, 1 row affected (0.17 sec)

Following is the query to display all records from the table using select statement:

mysql> select *from DemoTable;

This will produce the Following Output -

+----+---------------------+
| Id | ShippingDate        |
+----+---------------------+
| 1  | 2019-03-01 05:45:32 |
| 2  | 2019-04-13 11:34:56 |
| 3  | 2019-03-15 04:45:23 |
| 4  | 2019-04-11 12:10:02 |
+----+---------------------+
4 rows in set (0.00 sec)

Following is the query to get today's record using DATE from DATETIME field. Let’s say today’s date is “2019-04-13”:

mysql> select *from DemoTable where ShippingDate > date_sub(curdate(),interval 1 day);

This will produce the Following Output -

+----+---------------------+
| Id | ShippingDate        |
+----+---------------------+
| 2  | 2019-04-13 11:34:56 |
+----+---------------------+
1 row in set (0.00 sec)
raja
Published on 29-Apr-2019 08:37:08
Advertisements