Day of week: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
(→‎Weekday: + MySQL DAYOFWEEK() Function https://www.w3schools.com/sql/func_mysql_dayofweek.asp)
Line 24: Line 24:
| 5
| 5
| 6
| 6
|-
! [https://www.w3schools.com/sql/func_mysql_dayofweek.asp MySQL DAYOFWEEK() Function]
| 1
| 2
| 3
| 4
| 5
| 6
| 7
|-
|-
! [https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_weekday MySQL WEEKDAY() function]
! [https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_weekday MySQL WEEKDAY() function]

Revision as of 16:14, 24 December 2018

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

Weekday

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 DAYOFWEEK() Function 1 2 3 4 5 6 7
MySQL WEEKDAY() function 6 0 1 2 3 4 5
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

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 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 		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;

References

  1. ↑ PHP: How to get previous Sunday of a specific date in the past..? - Stack Overflow

Further reading

Retrieved from "https://wiki.planetoid.info/index.php?title=Day_of_week&oldid=20304"