Skip to content

Instantly share code, notes, and snippets.

@yuya-takeyama
Created October 2, 2013 02:11
Show Gist options
  • Save yuya-takeyama/6788163 to your computer and use it in GitHub Desktop.
Save yuya-takeyama/6788163 to your computer and use it in GitHub Desktop.
MySQL で特定の日時が何年の第何週で、その週は何日から何日までなのかを計算する (日曜起算)
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