Created
October 2, 2013 02:11
-
-
Save yuya-takeyama/6788163 to your computer and use it in GitHub Desktop.
MySQL で特定の日時が何年の第何週で、その週は何日から何日までなのかを計算する (日曜起算)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET | |
@DATETIME = NOW(); | |
SELECT | |
DATE(@DATETIME) AS `date`, | |
DATE_FORMAT(@DATETIME, '%X-W%V') AS `week`, | |
DATE_ADD(DATE_ADD(DATE(CONCAT(YEAR(@DATETIME), '-01-01')), INTERVAL (6 - WEEKDAY(DATE(CONCAT(YEAR(@DATETIME), '-01-01')))) DAY), INTERVAL ((WEEK(@DATETIME) - 1) * 7) DAY) AS `first_day_of_the_week`, | |
DATE_ADD(DATE_ADD(DATE(CONCAT(YEAR(@DATETIME), '-01-01')), INTERVAL (6 - WEEKDAY(DATE(CONCAT(YEAR(@DATETIME), '-01-01')))) DAY), INTERVAL (((WEEK(@DATETIME) - 1) * 7) + 6) DAY) AS `last_day_of_the_week` |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment