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.
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.
Solved, I re-wrote the stored procedure:
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
This routine does make use of other tables in my database and allows for companies to have different start of years.