Advertisement
Guest User

Untitled

a guest
Jun 19th, 2019
75
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.05 KB | None | 0 0
  1. DATE_SUB(
  2. DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK),
  3. INTERVAL WEEKDAY(
  4. DATE_ADD(MAKEDATE(year, 1), INTERVAL cw WEEK)
  5. ) -1 DAY),
  6.  
  7. SELECT STR_TO_DATE('2013 32 Tuesday', '%X %V %W');
  8.  
  9. '2013-08-13'
  10.  
  11. mysql> select DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK);
  12. +----------------------------------------------+
  13. | DATE_ADD(MAKEDATE(2011, 1), INTERVAL 1 WEEK) |
  14. +----------------------------------------------+
  15. | 2011-01-08 |
  16. +----------------------------------------------+
  17.  
  18. mysql> select date_add('2012-01-01', interval (8 - dayofweek('2011-01-01')) % 7 DAY);
  19. +------------------------------------------------------------------------+
  20. | date_add('2012-01-01', interval (8 - dayofweek('2011-01-01')) % 7 DAY) |
  21. +------------------------------------------------------------------------+
  22. | 2012-01-02 |
  23. +------------------------------------------------------------------------+
  24.  
  25. mysql> select date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week);
  26. +-----------------------------------------------------------------------------------------------------+
  27. | date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week) |
  28. +-----------------------------------------------------------------------------------------------------+
  29. | 2010-03-07 |
  30. +-----------------------------------------------------------------------------------------------------+
  31.  
  32. 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);
  33. +--------------------------------------------------------------------------------------------------------------------------------+
  34. | date_add( date_add( date_add('2010-01-01', interval (8 - dayofweek('2010-01-01')) % 7 DAY) , interval 9 week), interval 2 day) |
  35. +--------------------------------------------------------------------------------------------------------------------------------+
  36. | 2010-03-09 |
  37. +--------------------------------------------------------------------------------------------------------------------------------+
  38.  
  39. select
  40. date_add(
  41. date_add(
  42. date_add('<year>-01-01', interval (8 - dayofweek('<year>-01-01')) % 7 DAY)
  43. , interval <week-1> week)
  44. , interval <dayOfWeek> day
  45. );
  46.  
  47. SELECT STR_TO_DATE('2019 1 Monday', '%x %v %W') -- beginning of week
  48.  
  49. SELECT STR_TO_DATE('2019 1 Sunday', '%x %v %W') -- end of week
  50.  
  51. SET @week=1;
  52. SET @year=2014;
  53. SET @x_weeks_after_new_year=DATE_ADD(MAKEDATE(@year, 1), INTERVAL (SELECT IF(WEEKOFYEAR(MAKEDATE(@year, 1))>50 , 0 , -1))+@week WEEK);
  54. SELECT
  55. CONCAT(@year, '-', @week) WeekOfYear,
  56. @weekStart:=DATE_SUB(@x_weeks_after_new_year, INTERVAL WEEKDAY(@x_weeks_after_new_year) DAY) Monday,
  57. DATE_ADD(@weekStart, INTERVAL 6 DAY) Sunday
  58.  
  59. +------------+------------+------------+
  60. | WeekOfYear | Monday | Sunday |
  61. +------------+------------+------------+
  62. | 2014-1 | 2013-12-30 | 2014-01-05 |
  63. +------------+------------+------------+
  64.  
  65. SET DATEFIRST 1
  66. declare @wk int set @wk = 33
  67. declare @yr int set @yr = 2011
  68.  
  69. select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 2 -
  70. datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4) as date
  71.  
  72. 2011-08-16 00:00:00.000
  73.  
  74. DELIMITER $$
  75. CREATE FUNCTION fn_yearweek_to_date(
  76. var_yearweek INTEGER UNSIGNED,
  77. var_weekday ENUM(
  78. 'Monday',
  79. 'Tuesday',
  80. 'Wednesday',
  81. 'Thursday',
  82. 'Friday',
  83. 'Saturday',
  84. 'Sunday'
  85. )
  86. )
  87. RETURNS DATE DETERMINISTIC
  88. BEGIN
  89. RETURN STR_TO_DATE(CONCAT(var_yearweek, var_weekday), '%x%v%W');
  90. END;
  91. DELIMITER ;
  92.  
  93. SELECT
  94. fn_yearweek_to_date(YEARWEEK(NOW(), 1), 'Sunday'),
  95. fn_yearweek_to_date(YEARWEEK(NOW(), 1), 7)
  96. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement