Day of week

From LemonWiki共筆
Jump to navigation Jump to search

Numeric representation of the day of the week. MySQL, Excel or PHP approaches to specify these date time.

Weekday[edit]

The value of weekday obtained from different approaches.

Weekday values from different approaches Sunday Monday Tuesday Wednesday Thursday Friday Saturday
PHP date with N format character: Weekday (ISO-8601) 7 1 2 3 4 5 6
MySQL WEEKDAY() function 6 0 1 2 3 4 5
MySQL DAYOFWEEK() Function 1 2 3 4 5 6 7
Excel WEEKDAY() function 1 2 3 4 5 6 7
PHP date with w format character 0 1 2 3 4 5 6

Get the date[edit]

Get the date of weekday from another known date (today or another specific day)

Day Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Last week MySQL: SELECT CURDATE() + INTERVAL -8 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())-6
PHP: echo date('Y-m-d', strtotime("Sunday -14 day")) . PHP_EOL;
MySQL: SELECT CURDATE() + INTERVAL -7 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())-5
PHP: echo date('Y-m-d', strtotime("monday this week -7 day")) . PHP_EOL;
MySQL: SELECT CURDATE() + INTERVAL -3 - WEEKDAY(CURDATE()) DAY;
PHP: echo date('Y-m-d', strtotime('Friday -7 day')) . PHP_EOL;
MySQL: SELECT CURDATE() + INTERVAL -2 - WEEKDAY(CURDATE()) DAY;
PHP[1]: (1) echo date('Y-m-d', strtotime("last Saturday")) . PHP_EOL; (2) echo date('Y-m-d', strtotime('last Saturday', strtotime("2018-03-04 00:01:00"))) . PHP_EOL;
This week MySQL: SELECT CURDATE() + INTERVAL -1 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+1
PHP: echo date('Y-m-d', strtotime("Sunday -7 day")) . PHP_EOL;
MySQL: SELECT CURDATE() + INTERVAL 0 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+2
PHP: echo date('Y-m-d', strtotime("monday this week")) . PHP_EOL;
MySQL: SELECT CURDATE() + INTERVAL 1 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+3
MySQL: SELECT CURDATE() + INTERVAL 2 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+4
MySQL: SELECT CURDATE() + INTERVAL 3 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+5
MySQL: SELECT CURDATE() + INTERVAL 4 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+6
PHP: echo date('Y-m-d', strtotime('Friday')) . PHP_EOL;
MySQL: SELECT CURDATE() + INTERVAL 5 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+7
PHP: echo date('Y-m-d', strtotime("Saturday")) . PHP_EOL;
Next week MySQL: SELECT CURDATE() + INTERVAL 6 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+8
PHP: echo date('Y-m-d', strtotime("Sunday")) . PHP_EOL;
MySQL: SELECT CURDATE() + INTERVAL 7 - WEEKDAY(CURDATE()) DAY;
Excel: =TODAY()-WEEKDAY(NOW())+9
PHP: (1) echo date('Y-m-d', strtotime("next monday")) . PHP_EOL; (2) echo date('Y-m-d', strtotime("monday this week +7 day")) . PHP_EOL;
PHP: echo date('Y-m-d', strtotime('Friday +7 day')) . PHP_EOL;

References[edit]

Further reading[edit]