Day of week: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
 
(4 intermediate revisions by the same user not shown)
Line 33: Line 33:
| 4
| 4
| 5
| 5
|-
! [https://www.w3schools.com/sql/func_mysql_dayofweek.asp MySQL DAYOFWEEK() Function]
| 1
| 2
| 3
| 4
| 5
| 6
| 7
|-
|-
! [https://support.office.com/zh-tw/article/WEEKDAY-%E5%87%BD%E6%95%B8-60e44483-2ed1-439f-8bd0-e404c190949a Excel WEEKDAY() function]
! [https://support.office.com/zh-tw/article/WEEKDAY-%E5%87%BD%E6%95%B8-60e44483-2ed1-439f-8bd0-e404c190949a Excel WEEKDAY() function]
Line 72: Line 81:


! Last week
! Last week
| MySQL: {{kbd | key=<nowiki>SELECT CURDATE() + INTERVAL -8 - WEEKDAY(CURDATE()) DAY;</nowiki>}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())-6</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday -14 day")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-8</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())-6</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday -14 day")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=<nowiki>SELECT CURDATE() + INTERVAL -7 - WEEKDAY(CURDATE()) DAY;</nowiki>}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())-5</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week -7 day")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-7</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())-5</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week -7 day")) . PHP_EOL;</nowiki>}}
|  
|  
|  
|  
|  
|  
|  
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-3</span> - WEEKDAY(CURDATE()) DAY;}} <br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday -7 day')) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=<nowiki>SELECT CURDATE() - INTERVAL 2 - WEEKDAY(CURDATE()) DAY;</nowiki>}} <br /> PHP<ref>[https://stackoverflow.com/questions/3742820/php-how-to-get-previous-sunday-of-a-specific-date-in-the-past PHP: How to get previous Sunday of a specific date in the past..? - Stack Overflow]</ref>: (1) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("last Saturday")) . PHP_EOL;</nowiki>}} (2) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('last Saturday', strtotime("2018-03-04 00:01:00"))) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">-2</span> - WEEKDAY(CURDATE()) DAY;}} <br /> PHP<ref>[https://stackoverflow.com/questions/3742820/php-how-to-get-previous-sunday-of-a-specific-date-in-the-past PHP: How to get previous Sunday of a specific date in the past..? - Stack Overflow]</ref>: (1) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("last Saturday")) . PHP_EOL;</nowiki>}} (2) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('last Saturday', strtotime("2018-03-04 00:01:00"))) . PHP_EOL;</nowiki>}}
|-
|-
! This week
! This week
Line 86: Line 95:
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">2</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+4</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">2</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+4</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">3</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+5</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">3</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+5</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">4</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+6</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">4</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+6</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday')) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">5</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+7</nowiki>}} <br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Saturday")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">5</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+7</nowiki>}} <br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Saturday")) . PHP_EOL;</nowiki>}}
|-
|-
! Next week
! Next week
| MySQL: {{kbd | key=<nowiki>SELECT CURDATE() + INTERVAL 6 - WEEKDAY(CURDATE()) DAY;</nowiki>}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+8</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">6</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+8</nowiki>}}<br />PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("Sunday")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=<nowiki>SELECT CURDATE() + INTERVAL 7 - WEEKDAY(CURDATE()) DAY;</nowiki>}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+9</nowiki>}}<br />PHP: (1) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("next monday")) . PHP_EOL;</nowiki>}} (2) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week +7 day")) . PHP_EOL;</nowiki>}}
| MySQL: {{kbd | key=SELECT CURDATE() + INTERVAL <span class="hightlight">7</span> - WEEKDAY(CURDATE()) DAY;}} <br />Excel: {{kbd | key=<nowiki>=TODAY()-WEEKDAY(NOW())+9</nowiki>}}<br />PHP: (1) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("next monday")) . PHP_EOL;</nowiki>}} (2) {{kbd | key=<nowiki>echo date('Y-m-d', strtotime("monday this week +7 day")) . PHP_EOL;</nowiki>}}
|
|  
|  
|  
|  
|  
|  
| PHP: {{kbd | key=<nowiki>echo date('Y-m-d', strtotime('Friday +7 day')) . PHP_EOL;</nowiki>}}
|  
|  
|-
|-

Latest revision as of 18:47, 13 May 2019

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]

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

Further reading[edit]

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