Syntax error MySQL query to subtract date records with week day and display the weekday with records

MySQL query to subtract date records with week day and display the weekday with records

For this, you can use DATE_FORMAT(). Let us first create a table −

mysql> create table DemoTable1820
     (
     AdmissionDate varchar(20)
     );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1820 values('20/10/2019');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1820 values('19/12/2018');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1820 values('16/04/2017');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1820;

This will produce the following output −

+---------------+
| AdmissionDate |
+---------------+
| 20/10/2019    |
| 19/12/2018    |
| 16/04/2017    |
+---------------+
3 rows in set (0.00 sec)

Here is the query to subtract date records with week day and display week day name along with records −

mysql> select DATE_FORMAT(SUBDATE(STR_TO_DATE(AdmissionDate,'%d/%m/%y'), WEEKDAY(STR_TO_DATE(AdmissionDate,'%d/%m/%y'))), '%a %d %b') from DemoTable1820;

This will produce the following output −

+-------------------------------------------------------------------------------------------------------------------------+
| DATE_FORMAT(SUBDATE(STR_TO_DATE(AdmissionDate,'%d/%m/%y'), WEEKDAY(STR_TO_DATE(AdmissionDate,'%d/%m/%y'))), '%a %d %b') |
+-------------------------------------------------------------------------------------------------------------------------+
| Mon 19 Oct                                                                                                              |
| Mon 14 Dec                                                                                                              |
| Mon 13 Apr                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------+
3 rows in set, 6 warnings (0.00 sec)
Updated on: 2019-12-24T06:28:24+05:30

227 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements