SPlatten SPlatten - 1 year ago 39
SQL Question

MariaDB start of week date & week number 1 to 52

I've written a stored procedure to get the week from a date, it also returns the date at the start of the week as well as the week number and year.

I'm aware of the 'WEEK' function, however this doesn't give me the date at the start of the week and I'm not aware of a function that does this given the week and year.

Question is:

How can I get the 'date' at the start of the week given the week number? Where the start of the week is passed in as a day index, 0 = Sunday, 1 = Monday etc.

My current function doesn't always work and if the first day of the week is Monday, then Sunday falls into the next week, not the end of the same week as I would like it to be.

Answer Source

Solved, I re-wrote the stored procedure:

    # 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');

    #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');  

    #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
    #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
        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); 

This routine does make use of other tables in my database and allows for companies to have different start of years.