LastSaturdaysInRangeSQL ( startDate ; endDate )

Returns a comma-separated list of SQL-formatted dates representing the last Saturday of each month between the start and end dates.

Be the first to rate this function Log in to vote

Jonni™ - Show more from this author

Sample input:

LastSaturdaysInRangeSQL ( Get ( CurrentDate ) ; Get ( CurrentDate ) + 365 )

Sample output:

'2024-01-27', '2024-02-24', '2024-03-30', '2024-04-27', ...

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// LAST SATURDAYS IN RANGE SQL

// Returns a comma-separated list of SQL-formatted dates representing 
// the last Saturday of each month between the start and end dates.

// NOTE : The segments are stacked, so make sure to order the array starting with the lowest percentages 

// Function Name : LastSaturdaysInRangeSQL
// Parameters : startDate ; endDate
// Use example : LastSaturdaysInRangeSQL ( Get ( CurrentDate ) ; Get ( CurrentDate ) + 365 )

Let ( [
    y = Year ( startDate ) ;
    m = Month ( startDate ) ;
    firstOfMonth = Date ( m ; 1 ; y ) ;
    lastOfMonth = Date ( m + 1 ; 0 ; y ) ;
    lastDow = DayOfWeek ( lastOfMonth ) ;
    lastSaturday = lastOfMonth - Mod ( lastDow ; 7 ) ;
    nextMonth = Date ( m + 1 ; 1 ; y ) ;
    sqlDate = "'" & Year ( lastSaturday ) & "-" & 
                  Right ( "0" & Month ( lastSaturday ) ; 2 ) & "-" & 
                  Right ( "0" & Day ( lastSaturday ) ; 2 ) & "'"
];
    If (
        firstOfMonth > endDate ;
        "" ;
        sqlDate & 
        Case (
            Date ( m + 1 ; 1 ; y ) > endDate ; "" ; ", "
        ) &
        LastSaturdaysInRangeSQL ( nextMonth ; endDate )
    )
)

// Generates a list of dates in SQL 'YYYY-MM-DD' format for the last Saturday 
// of each month within a given date range. This function uses recursion to 
// iterate through months, making it compatible with FileMaker versions prior to 
// 18 which do not support the While function. 

// The resulting list is formatted for direct use in SQL IN (...) clauses.

// GitHub : https://github.com/roxytheman/fmCustomFunctions/blob/6b779dd763055baf55df517c0bfdb84d9233b91e/functions/LastSaturdaysInRangeSQL.fmfn
// BD Link : https://www.briandunning.com/cf/2799
// Written by: jonni@me.com
// V 1.0

Generates a list of dates in SQL 'YYYY-MM-DD' format for the last Saturday of each month within a given date range. This function uses recursion to iterate through months, making it compatible with FileMaker versions prior to 18 which do not support the While function.

The resulting list is formatted for direct use in SQL IN (...) clauses.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

LastSaturdaysInRangeSQL ( startDate ; endDate )

Comments

Support this website.

Support this website.