0

Is is possible to get the DAYOFMONTH for the first date of a week given the year, the month and the week?
Mind that the first date of a week might as well be on a previous month and even in a previous year.

I know that this is possible programmatically, at least using java.time, TemporalFields, etc.. but I'd like to do it on sql query level since when weeks are involved there are two extra parameters involved in the process which are the DayOfWeek that is considered the first day of a week and the minimum days of the to account a week inside a month. Which could lead to different results if the configuration is not exactly the same both on sql and in the api.

Examples using DayOfMonth.Monday and minimum days for the first week:1,
which in terms of sql should be the mode 7 of the WEEK MariaDb function.

Given Year: 2020 Given Month: 1 Given Week: 1 Result: 30
Given Year: 2020 Given Month: 1 Given Week: 2 Result: 6
Given Year: 2020 Given Month: 1 Given Week: 3 Result: 13
Given Year: 2020 Given Month: 1 Given Week: 4 Result: 20
Given Year: 2020 Given Month: 1 Given Week: 5 Result: 27
Given Year: 2020 Given Month: 2 Given Week: 5 Result: 27
Given Year: 2020 Given Month: 2 Given Week: 6 Result: 3
Given Year: 2020 Given Month: 2 Given Week: 7 Result: 10
Given Year: 2020 Given Month: 2 Given Week: 8 Result: 17
Given Year: 2020 Given Month: 2 Given Week: 9 Result: 24
Given Year: 2020 Given Month: 3 Given Week: 9 Result: 24
Given Year: 2020 Given Month: 3 Given Week: 10 Result: 2
Given Year: 2020 Given Month: 3 Given Week: 11 Result: 9
Given Year: 2020 Given Month: 3 Given Week: 12 Result: 16
Given Year: 2020 Given Month: 3 Given Week: 13 Result: 23
Given Year: 2020 Given Month: 3 Given Week: 14 Result: 30
Given Year: 2020 Given Month: 4 Given Week: 14 Result: 30
Given Year: 2020 Given Month: 4 Given Week: 15 Result: 6
Given Year: 2020 Given Month: 4 Given Week: 16 Result: 13
Given Year: 2020 Given Month: 4 Given Week: 17 Result: 20
Given Year: 2020 Given Month: 4 Given Week: 18 Result: 27
Given Year: 2020 Given Month: 5 Given Week: 18 Result: 27
Given Year: 2020 Given Month: 5 Given Week: 19 Result: 4
Given Year: 2020 Given Month: 5 Given Week: 20 Result: 11
Given Year: 2020 Given Month: 5 Given Week: 21 Result: 18
Given Year: 2020 Given Month: 5 Given Week: 22 Result: 25
Given Year: 2020 Given Month: 6 Given Week: 23 Result: 1
Given Year: 2020 Given Month: 6 Given Week: 24 Result: 8
Given Year: 2020 Given Month: 6 Given Week: 25 Result: 15
Given Year: 2020 Given Month: 6 Given Week: 26 Result: 22
Given Year: 2020 Given Month: 6 Given Week: 27 Result: 29
Given Year: 2020 Given Month: 7 Given Week: 27 Result: 29
Given Year: 2020 Given Month: 7 Given Week: 28 Result: 6
Given Year: 2020 Given Month: 7 Given Week: 29 Result: 13
Given Year: 2020 Given Month: 7 Given Week: 30 Result: 20
Given Year: 2020 Given Month: 7 Given Week: 31 Result: 27
Given Year: 2020 Given Month: 8 Given Week: 31 Result: 27
Given Year: 2020 Given Month: 8 Given Week: 32 Result: 3
Given Year: 2020 Given Month: 8 Given Week: 33 Result: 10
Given Year: 2020 Given Month: 8 Given Week: 34 Result: 17
Given Year: 2020 Given Month: 8 Given Week: 35 Result: 24
Given Year: 2020 Given Month: 8 Given Week: 36 Result: 31
Given Year: 2020 Given Month: 9 Given Week: 36 Result: 31
Given Year: 2020 Given Month: 9 Given Week: 37 Result: 7
Given Year: 2020 Given Month: 9 Given Week: 38 Result: 14
Given Year: 2020 Given Month: 9 Given Week: 39 Result: 21
Given Year: 2020 Given Month: 9 Given Week: 40 Result: 28
Given Year: 2020 Given Month: 10 Given Week: 40 Result: 28
Given Year: 2020 Given Month: 10 Given Week: 41 Result: 5
Given Year: 2020 Given Month: 10 Given Week: 42 Result: 12
Given Year: 2020 Given Month: 10 Given Week: 43 Result: 19
Given Year: 2020 Given Month: 10 Given Week: 44 Result: 26
Given Year: 2020 Given Month: 11 Given Week: 44 Result: 26
Given Year: 2020 Given Month: 11 Given Week: 45 Result: 2
Given Year: 2020 Given Month: 11 Given Week: 46 Result: 9
Given Year: 2020 Given Month: 11 Given Week: 47 Result: 16
Given Year: 2020 Given Month: 11 Given Week: 48 Result: 23
Given Year: 2020 Given Month: 11 Given Week: 49 Result: 30
Given Year: 2020 Given Month: 12 Given Week: 49 Result: 30
Given Year: 2020 Given Month: 12 Given Week: 50 Result: 7
Given Year: 2020 Given Month: 12 Given Week: 51 Result: 14
Given Year: 2020 Given Month: 12 Given Week: 52 Result: 21
Given Year: 2020 Given Month: 12 Given Week: 53 Result: 28
Christos Karapapas
  • 1,018
  • 3
  • 19
  • 40
  • The "given month" is redundant, right, or am I missing something? See [How to convert number of week into date?](https://stackoverflow.com/q/7078730) to get, in that case, the date for the tuesday of the week. – Solarflare Aug 19 '20 at 15:23
  • @Solarflare Unfortunately it seems there is something slightly wrong with `STR_TO_DATE` read my comment to indago. – Christos Karapapas Aug 19 '20 at 17:11

1 Answers1

0

I do feel the significance of having this but I haven't find any built-in function in MySQL/MariaDB that can I can directly use (like WEEK() etc. if there's one). In my case, I'm using a custom query to generate the week number and so far, the query below have been returning correct week number:

SELECT mnd AS 'StartDate', 
       mxd AS 'EndDate', 
       ROW_NUMBER() OVER (PARTITION BY  YEAR(mxd) ORDER BY mnd) AS 'WeekNo' 
FROM
(SELECT MIN(dt) AS mnd,MAX(dt) AS mxd, nom FROM
 (SELECT dt, num , CEIL(num/7) AS nom FROM
  (SELECT DATE_FORMAT(CONCAT_WS('-',yr,mnt,dy),'%Y-%m-%d') AS dt, 
          ROW_NUMBER() OVER (ORDER BY dt) AS num 
      FROM
      /*Query to generate date in MariaDB*/
      (SELECT LPAD(seq,2,0) AS dy FROM seq_1_to_31) a,
      (SELECT LPAD(seq,2,0) AS mnt FROM seq_1_to_12) b,
      (SELECT seq AS yr FROM seq_2018_to_2023) c
      /*-------------------------*/
HAVING dt IS NOT NULL ORDER BY dt) V ) W 
GROUP BY nom) Q;

Demo fiddle here

I did ROW_NUMBER() first in the subquery then use that result to divide by 7 (as 7 days a week) with ceiling function (CEIL()). Then do another query to group by result of the ceiling function with MIN() and MAX() over the date. I did another ROW_NUMBER() function over the query with PARTITION by year of the EndDate. All that but the most important thing is to select the first year where 1/January = Monday; in the example above, 01/01/2018 was on Monday.

FanoFN
  • 6,815
  • 2
  • 13
  • 33