MySQL - convert date to weekday number
In this article, we would like to show you how to convert date to weekday number in MySQL.
Quick solution:
xxxxxxxxxxSELECT DAYOFWEEK(date_expression) AS 'dayofweek';or
xxxxxxxxxxSELECT WEEKDAY(date_expression) AS 'weekday';Note:
Go to this article to read about differences between
DAYOFWEEK()andWEEKDAY()functions.
In this example, we will convert example date ( 2021-01-01 11:41:31 - Friday) to weekday number.
Query:
xxxxxxxxxxSELECT DAYOFWEEK('2021-01-01 11:41:31') AS 'dayofweek';Result:
Query:
xxxxxxxxxxSELECT WEEKDAY('2021-01-01 11:41:31') AS 'weekday';Result:
To show how to convert date to weekday number, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will convert registration_time column from users table to the day week number using DAYOFWEEK() function.
Query:
xxxxxxxxxxSELECT `id`, `username`, `registration_time`, DAYOFWEEK(`registration_time`) AS 'dayofweek'FROM `users`;Result:
In this example, we will convert registration_time column from users table to the day week number using WEEKDAY() function.
Query:
xxxxxxxxxxSELECT `id`, `username`, `registration_time`, WEEKDAY(`registration_time`) AS 'weekday'FROM `users`;Result:
create_tables.sql file:
xxxxxxxxxxCREATE TABLE `users` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(50) NOT NULL, `registration_time` DATETIME NOT NULL, PRIMARY KEY (`id`));insert_data.sql file:
xxxxxxxxxxINSERT INTO `users` (`username`, `registration_time`)VALUES ('Tom', '2021-01-01 11:41:31'), ('Chris','2021-01-02 11:42:45'), ('Jack','2021-01-03 15:13:39'), ('Kim','2021-01-03 15:24:51'), ('Marco','2021-01-04 22:35:38'), ('Kate','2021-01-04 22:46:51'), ('Nam','2021-01-04 22:57:37');