Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Get Nth weekday of the month from a column with DATE records in MySQL
We need to find the weekday i.e. week 1 from date 1 to 7, week 2 from date 8 to 14, etc. To get the day, use DAY() function in MySQL. Set the conditions to get the weekday (number) using CASE statement.
Let us now see an example and create a table −
mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.63 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values('2019-09-12');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('2019-09-06');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('2019-09-26');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values('2019-09-30');
Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+---------------+ | AdmissionDate | +---------------+ | 2019-09-12 | | 2019-09-06 | | 2019-09-26 | | 2019-09-30 | +---------------+ 4 rows in set (0.00 sec)
Following is the query to get the weekday (number) of the month −
mysql> select ( CASE WHEN DAY(AdmissionDate) BETWEEN 1 AND 7 THEN 1 WHEN DAY(AdmissionDate) BETWEEN 8 AND 14 THEN 2 WHEN DAY(AdmissionDate) BETWEEN 15 AND 21 THEN 3 WHEN DAY(AdmissionDate) BETWEEN 22 AND 28 THEN 4 else 5 end ) as NthWeekDayOfMonth from DemoTable;
This will produce the following output −
+-------------------+ | NthWeekDayOfMonth | +-------------------+ | 2 | | 1 | | 4 | | 5 | +-------------------+ 4 rows in set (0.01 sec)
Updated on: 07-Oct-2019
178 Views
Advertisements