Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DATE_SUB(
- DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK),
- INTERVAL WEEKDAY(
- DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)
- ) -1 DAY),
- SELECT STR_TO_DATE('2013 32 Tuesday', '%X %V %W');
- '2013-08-13'
- mysql> select DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK);
- +----------------------------------------------+
- | DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK) |
- +----------------------------------------------+
- | 2011-01-08 |
- +----------------------------------------------+
- mysql> select date_add('2012-01-01', interval (8 - dayofweek('2011-01-01')) % 7 DAY);
- +------------------------------------------------------------------------+
- | date_add('2012-01-01', interval (8 - dayofweek('2011-01-01')) % 7 DAY) |
- +------------------------------------------------------------------------+
- | 2012-01-02 |
- +------------------------------------------------------------------------+
- mysql> select date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week);
- +-----------------------------------------------------------------------------------------------------+
- | date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week) |
- +-----------------------------------------------------------------------------------------------------+
- | 2010-03-07 |
- +-----------------------------------------------------------------------------------------------------+
- mysql> select date_add( date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week), interval 2 day);
- +--------------------------------------------------------------------------------------------------------------------------------+
- | date_add( date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week), interval 2 day) |
- +--------------------------------------------------------------------------------------------------------------------------------+
- | 2010-03-09 |
- +--------------------------------------------------------------------------------------------------------------------------------+
- select
- date_add(
- date_add(
- date_add('<year>-01-01', interval (8 - dayofweek('<year>-01-01')) % 7 DAY)
- , interval <week-1> week)
- , interval <dayOfWeek> day
- );
- SELECT STR_TO_DATE('2019 1 Monday', '%x %v %W') -- beginning of week
- SELECT STR_TO_DATE('2019 1 Sunday', '%x %v %W') -- end of week
- SET @week=1;
- SET @year=2014;
- SET @x_weeks_after_new_year=DATE_ADD(MAKEDATE(@year, 1), INTERVAL (SELECT IF(WEEKOFYEAR(MAKEDATE(@year, 1))>50 , 0 , -1))+@week WEEK);
- SELECT
- CONCAT(@year, '-', @week) WeekOfYear,
- @weekStart:=DATE_SUB(@x_weeks_after_new_year, INTERVAL WEEKDAY(@x_weeks_after_new_year) DAY) Monday,
- DATE_ADD(@weekStart, INTERVAL 6 DAY) Sunday
- +------------+------------+------------+
- | WeekOfYear | Monday | Sunday |
- +------------+------------+------------+
- | 2014-1 | 2013-12-30 | 2014-01-05 |
- +------------+------------+------------+
- SET DATEFIRST 1
- declare @wk int set @wk = 33
- declare @yr int set @yr = 2011
- select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 2 -
- datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4) as date
- 2011-08-16 00:00:00.000
- DELIMITER $$
- CREATE FUNCTION fn_yearweek_to_date(
- var_yearweek INTEGER UNSIGNED,
- var_weekday ENUM(
- 'Monday',
- 'Tuesday',
- 'Wednesday',
- 'Thursday',
- 'Friday',
- 'Saturday',
- 'Sunday'
- )
- )
- RETURNS DATE DETERMINISTIC
- BEGIN
- RETURN STR_TO_DATE(CONCAT(var_yearweek, var_weekday), '%x%v%W');
- END;
- DELIMITER ;
- SELECT
- fn_yearweek_to_date(YEARWEEK(NOW(), 1), 'Sunday'),
- fn_yearweek_to_date(YEARWEEK(NOW(), 1), 7)
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement