我编写了一个存储过程来从日期获取周，它还返回一周开始时的日期以及周数和年份。
我知道"WEEK"功能，但是这不会给我一周开始时的日期，而且我不知道给定周和年份的函数可以做到这一点。
问题是：
如何在给定周数的情况下获取一周开始时的"日期"？ 其中一周的开始作为日索引传入，0 = 星期日，1 = 星期一等。
我当前的函数并不总是有效，如果一周的第一天是星期一，那么星期日就落入下周，而不是我希望的同一周的结束。
我也在挖掘这个问题。但是我偶然发现了一些同样有效的mysql代码。它基本上根据星期几减去天数。即，如果日期是星期三 （4），您知道日期是 1-4=-3 天前。
这个怎么样：
# with Sunday being the start of the week:
select convert(date_add(now(), interval(1-dayofweek(now())) day), date) as WeekStartDate
select convert(date_add(now(), interval(7-dayofweek(now())) day), date) as WeekEndDate
# with Monday being the start of the week:
select convert(date_add(now(), interval(2-dayofweek(now())) day), date) as WeekStartDate
select convert(date_add(now(), interval(8-dayofweek(now())) day), date) as WeekEndDate
信用：如何在 mysql 中获取日期的一周的第一天？
使用序列引擎。您可以根据需要调整以下示例：
MariaDB [_]> SHOW ENGINESG
.
.
.
*************************** 3. row ***************************
Engine: SEQUENCE
Support: YES
Comment: Generated tables filled with sequential values
Transactions: YES
XA: NO
Savepoints: YES
.
.
.
MariaDB [_]> SET @`year` := 2016,
-> @`mode` := 1,
-> @`week` := 23;
Query OK, 0 rows affected (0.00 sec)
MariaDB [_]> SELECT
-> `der`.`date`,
-> `der`.`week`,
-> `der`.`year`
-> FROM (
-> SELECT
-> `der`.`date`,
-> WEEK(`der`.`date`, @`mode`) `week`,
-> YEAR(`der`.`date`) `year`
-> FROM (
-> SELECT
-> DATE_ADD(CONCAT(@`year`, '-01-01'), INTERVAL `s`.`seq` DAY) `date`
-> FROM
-> seq_0_to_365 `s`
-> ) `der`
-> ) `der`
-> WHERE
-> `der`.`week` = @`week` AND
-> `der`.`year` = @`year`;
+------------+------+------+
| date | week | year |
+------------+------+------+
| 2016-06-06 | 23 | 2016 |
| 2016-06-07 | 23 | 2016 |
| 2016-06-08 | 23 | 2016 |
| 2016-06-09 | 23 | 2016 |
| 2016-06-10 | 23 | 2016 |
| 2016-06-11 | 23 | 2016 |
| 2016-06-12 | 23 | 2016 |
+------------+------+------+
7 rows in set (0.01 sec)
解决了，我重写了存储过程：
exitProc:BEGIN
#--
# Procedure:
# weekFromDate
#
# Parameters:
# vcCompKey, the key associated with the company
# dtDate, the date to translate
# dtOutSOW, returned start of week date
# siOutWeek, returned week number
# siOutYear, returned year
#--
DECLARE siDIY SMALLINT; #Day in year
DECLARE siFDOW SMALLINT; #First day of week
DECLARE siGoBack SMALLINT; #Flag used to check for last year
DECLARE siRmonth SMALLINT; #Reference Month
DECLARE siRyear SMALLINT; #Reference Year
DECLARE dtSOY DATE; #Date of start of year
DECLARE vcFMDOY VARCHAR(12);#First month and day of year
DECLARE vcFDOW VARCHAR(12);#First day of the week
DECLARE vcDYSOW VARCHAR(80);#Days of week
#Get the first day of the week for the specified company
SET vcFDOW = vcGetParamValue(vcCompKey, 'Var:First day of week');
IF (vcFDOW IS NULL) THEN
#No entry found, abort!
LEAVE exitProc;
END IF;
#Get the first month and day of the year for the specified company
SET vcFMDOY = vcGetParamValue(vcCompKey, 'Var:First day of year');
IF (vcFMDOY IS NULL) THEN
#No entry found, abort!
LEAVE exitProc;
END IF;
#Set-up days of week
SET vcDYSOW = 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday';
#Get the first day of the week index base 1
SET siFDOW = FIND_IN_SET(LOWER(vcFDOW), LOWER(vcDYSOW)) - 1;
#Get the reference month and year
SET siRmonth = MONTH(dtDate);
SET siRyear = YEAR(dtDate);
SET dtSOY = DATE(CONCAT(siRyear, '/', vcFMDOY));
#Calculate the start of week date
SET dtOutSOW = DATE_SUB(dtDate, INTERVAL (DAYOFWEEK(dtDate) - siFDOW) DAY) + 1;
#Calculate the day in year
SET siDIY = DATEDIFF(dtOutSOW, dtSOY);
#Do we need to go back to the end of the previous year?
SET siGoBack = YEAR(dtDate) - YEAR(dtOutSOW);
IF siGoBack < 0 Or siDIY < 0 Or dtDate < dtOutSOW THEN
#Yes
IF YEAR(dtOutSOW) = YEAR(dtDate) THEN
SET dtOutSOW = DATE_SUB(dtOutSOW, INTERVAL 7 DAY);
END IF;
SET dtSOY = DATE(CONCAT(YEAR(dtOutSOW), '/', vcFMDOY));
SET siDIY = DATEDIFF(dtOutSOW, dtSOY);
END IF;
#Calculate the week no. and year
SET siOutWeek = (siDIY / 7) + 1;
SET siOutYear = YEAR(dtOutSOW);
END
此例程确实利用了我数据库中的其他表，并允许公司有不同的年份开始。
作为测试，我会找到本周的开始，第一个注意事项：
mysql> SELECT NOW(), WEEK(NOW());
+---------------------+-------------+
| NOW() | WEEK(NOW()) |
+---------------------+-------------+
| 2016-06-18 12:10:58 | 24 |
+---------------------+-------------+
那么这就是函数的实质：
mysql> SELECT '2016-01-01'
+ INTERVAL 7*24
- DAYOFWEEK('2016-01-01')
+ 1 DAY;
+----------------------------------------------------------------+
| '2016-01-01' + INTERVAL 7*24 - DAYOFWEEK('2016-01-01') + 1 DAY |
+----------------------------------------------------------------+
| 2016-06-12 |
+----------------------------------------------------------------+
'2016-01-01'是有问题的一年的开始。
24是WEEK()数字。
+ 1 DAY是为了补偿一周的开始。
需要做其他事情来处理您对一周开始的哪一天的选择。
对 user1014010 的答案进行了一些更正。当一周从星期一开始时，您将收到下周星期日的日期。这是我的更正：
SELECT DATE(DATE_ADD(NOW(), INTERVAL -((5 + DAYOFWEEK(NOW())) % 7) DAY)) AS WeekStartDate