Last weekdays records from date field of MySQL table by dayofweek function

To get the number of weekday ( from 1 to 7 ) we will use dayofweek function of MySQL.

DAYOFWEEK(date)

Example

SELECT DAYOFWEEK('2016-05-06')# Output is 6

1 = Sunday , 2 = Monday, 3 = Tuesday, 4 = Wednesday , 5 = Thursday, 6 = Friday, 7 = Saturday

For a invalid input date, dayofweek() will return NULL
DEMO: Select date from Calendar to execute DAYOFWEEK()

DAYOFWEEK() function to get number from 1 to 7 based on Weekday in MySQL

Filter records of all weekdays up to today

This function dayofweek returns values 1 to 7 based on the weekday starting from Sunday as 1, Monday as 2 and …so on for others. So if today is Thursday then dayofweek function will return 5.

So we need before three days record ( including today ) from today to get the records stating from Monday. So we will deduct 2 ( Monday value ) from the weekday figure. Here is the query to get the records of all weekdays of a week till today.

SELECT id,date,weekday, dayofweek(date) as dayofweek 
FROM dt_weekday 
WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-2) DAY ) AND CURDATE( )

Above query will collect records starting from Monday and ending with today. For example if today is Tuesday then you will get two records ( of Monday and Tuesday )

Below query will return the Date of Monday of present week.

SELECT DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-2) DAY )

Please read the tutorial on last x day's record in part 1 of this tutorial. Here we will develop a query to get records of weekdays of the present week.

For your understanding we have displayed dayofweek value against each record.

More about DATE_SUB()

Using weekday function

By using weekday function we will get the same result but here weekday will return different number for days.

Monday = 0, Tuesday = 1  and so on ...

Here is a simple query

SELECT WEEKDAY(  '2014-08-15' )

Output of above query is 4 . (15th Aug 2014 is Friday).

Here is the query using weekday function to get records of the week starting from Monday.

SELECT id, DATE, weekday, WEEKDAY( CURDATE( ) ) AS weekday 
FROM dt_weekday 
WHERE  `date` BETWEEN DATE_SUB( CURDATE( ) , INTERVAL( WEEKDAY( CURDATE( ) ) ) DAY ) AND CURDATE( )

Previous One week records

By adjusting the interval in our above query we can return the records of previous Week starting from Monday to Saturday.

SELECT id, DATE, weekday, DAYOFWEEK( CURDATE( ) ) AS dayofweek 
FROM dt_weekday 
WHERE `date` 
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL (dayofweek(CURDATE())+5) 
DAY ) 
AND DATE_SUB( CURDATE( ) , INTERVAL (dayofweek(CURDATE())) 
DAY )

Previous Two week records

This query can be further adjusted to return previous two weeks record. ( Check the difference with previous query )

SELECT id, DATE, weekday, DAYOFWEEK( CURDATE( ) ) AS dayofweek
FROM dt_weekday
WHERE  `date` 
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL( DAYOFWEEK( CURDATE( ) ) +12 ) 
DAY ) 
AND DATE_SUB( CURDATE( ) , INTERVAL( DAYOFWEEK( CURDATE( ) ) +7 ) 
DAY )

Dynamic SQL Dump

Here is the sql dump to create table dt_weekday. This dump is dynamically created by considering previous 15 days and next 15 days starting from today. You will have one record for each day.

You should always take a fresh dump file from here for your testing, if you are not executing on same day.

One column weekday is kept to store name of the weekday ( sun, mon, tue … ) for your understanding.

CREATE TABLE IF NOT EXISTS `dt_weekday` (

  `id` varchar(3) NOT NULL,

  `date` date NOT NULL,

  `weekday` varchar(3) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `dt_weekday` VALUES ('-15', '2025-10-30','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('-14', '2025-10-29','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('-13', '2025-10-28','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('-12', '2025-10-27','Mon' ); 
INSERT INTO `dt_weekday` VALUES ('-11', '2025-10-26','Sun' ); 
INSERT INTO `dt_weekday` VALUES ('-10', '2025-10-25','Sat' ); 
INSERT INTO `dt_weekday` VALUES ('-9', '2025-10-24','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('-8', '2025-10-23','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('-7', '2025-10-22','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('-6', '2025-10-21','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('-5', '2025-10-20','Mon' ); 
INSERT INTO `dt_weekday` VALUES ('-4', '2025-10-19','Sun' ); 
INSERT INTO `dt_weekday` VALUES ('-3', '2025-10-18','Sat' ); 
INSERT INTO `dt_weekday` VALUES ('-2', '2025-10-17','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('-1', '2025-10-16','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('0', '2025-10-15','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('1', '2025-10-14','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('2', '2025-10-13','Mon' ); 
INSERT INTO `dt_weekday` VALUES ('3', '2025-10-12','Sun' ); 
INSERT INTO `dt_weekday` VALUES ('4', '2025-10-11','Sat' ); 
INSERT INTO `dt_weekday` VALUES ('5', '2025-10-10','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('6', '2025-10-09','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('7', '2025-10-08','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('8', '2025-10-07','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('9', '2025-10-06','Mon' ); 
INSERT INTO `dt_weekday` VALUES ('10', '2025-10-05','Sun' ); 
INSERT INTO `dt_weekday` VALUES ('11', '2025-10-04','Sat' ); 
INSERT INTO `dt_weekday` VALUES ('12', '2025-10-03','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('13', '2025-10-02','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('14', '2025-10-01','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('15', '2025-09-30','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('16', '2025-09-29','Mon' );

SQL txt file for DAYOFWEEK()
SQL Date References MONTH() DAY() LAST_DAY() : Last Day of the month WEEKDAY() : 0 for Monday .. 6 for Sunday

Subhendu Mohapatra

Author

ðŸŽ¥ Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and projectâ€‘oriented with real examples and source code.

Subscribe to our YouTube Channel here

dayofweek() : number of weekday ( from 1 to 7 )