/******************************************************************************
  2 Examples of getting the last Friday of the month
******************************************************************************/
/*=======================================================================
  Function to return last Friday of the month
========================================================================*/
DROP FUNCTION last_friday;
/
CREATE FUNCTION last_friday(in_date DATE)  RETURNS date  
BEGIN
    DECLARE v_last_day    DATE;
    DECLARE v_last_friday DATE; 

    SET v_last_day    = LAST_DAY(in_date);        --last day of the month
    SET v_last_friday =  
        CASE  DAYOFWEEK(v_last_day)                            /* 1-7 */
            WHEN 1 THEN TIMESTAMPADD( DAY, -2, v_last_day)      /*Sun*/
            WHEN 2 THEN TIMESTAMPADD( DAY, -3, v_last_day)      /*Mon*/
            WHEN 3 THEN TIMESTAMPADD( DAY, -4, v_last_day)      /*Tue*/
            WHEN 4 THEN TIMESTAMPADD( DAY, -5, v_last_day)      /*Wed*/
            WHEN 5 THEN TIMESTAMPADD( DAY, -6, v_last_day)      /*Thu*/
            WHEN 6 THEN TIMESTAMPADD( DAY,  0, v_last_day)      /*Fri*/
            WHEN 7 THEN TIMESTAMPADD( DAY, -1, v_last_day)      /*Sat*/
        END; 

    RETURN v_last_friday; 
END 
/   

SELECT last_friday(now());
/



/*=======================================================================
  Function to return last Friday of the month
========================================================================*/
DROP FUNCTION last_friday2;
/
CREATE FUNCTION last_friday2(in_date DATE)  RETURNS date  
BEGIN
    DECLARE v_last_day    DATE; 
    DECLARE v_day_offset  INT; 
    DECLARE v_last_friday DATE;
 
    SELECT LAST_DAY(in_date)     into v_last_day; 
    SELECT DAYOFWEEK(v_last_day) into v_day_offset; 
 
    SET v_day_offset = IF(v_day_offset>=6, v_day_offset-6, v_day_offset+1); 
 
    SELECT TIMESTAMPADD(DAY, -v_day_offset, v_last_day) into v_last_friday;
    RETURN v_last_friday;
END  
/   

SELECT last_friday2(now());