SQLMonthRange

Function SQLMonthRange($field, $month=0, $formatOut="M", $relative=true, $between=true) {
    /*
     * This is designed to be called in the middle of an SQL query to simplify
     * both the readability and repeatability of the query.  The SQL query is
     * assumed to be calling to capture values for a given field ($field)
     * during the given month ($month).
     *
     * A sample call might be:
     *
     * $months =SQLMonthRange('COLUMN','11','M');
     *
     * A sample output might be:
     *
     * ( COLUMN BETWEEN '2007-Nov-01' AND '2007-Dec-01' )
     *
     * when using the default $between = true, or
     *
     * ( COLUMN >= '2007-Nov-01' and COLUMN < '2007-Dec-01' )
     *
     * when using $default = false
     *
     * $month is any valid (positive or negative) integer.
     * In relative mode ($relative=true), 0 is the current month.
     * In absolute mode ($relative=false) 0 is December of the previous year
     *
     * RELATIVE MODE
     * $relative (true by default) allows us to keep a sliding month scale so that
     * either positive or negative values reflect the current month plus the value
     * of $month.  For example, if the present month is March, a $month value of
     * -2 refers to January (two months prior to March), and a $month value of 5
     * refers to August (5 months after March).
     *
     * Be careful in absolute mode (where $relative is false).  In absolute mode,
     * a $month value of -1 is NOT December.  Remember that since 1 is January, it
     * follows that 0 must be December (again, this example is for absolute mode).
     *
     * $month =   0 ==> December of the previous year in  absolute mode ($relative=false)
     * $month =  12 ==> December of the current year in absolute mode ($relative=false)
     * $month = -11 ==> January of the previous year in absolute mode ($relative=false)
     *
     * $month =   0 ==> current month in relative mode ($relative=true)
     * $month =  12 ==> 12 months ahead of current month in relative mode ($relative=true)
     * $month = -11 ==> 11 months prior to current month in relative mode ($relative=true)
     *
     * BETWEEN
     * $between allows us to switch to a BETWEEN statement or use the >= / <=
     * combination, which may be useful when comparing speed tests.  Also,
     * BETWEEN is inclusive of the beginning and ending values.  That is, it does
     * in fact have an implicit ">=" at the beginning value and a "<=" at the
     * ending value (not just a ">" and a "<").
      // http://msdn2.microsoft.com/en-us/library/aa225976(SQL.80).aspx

     */

    $field = trim($field);
    $monthPlusOne = $month + 1;
    $variableMonth = "+$month";
    if ($relative == false) { //ABSOLUTE MONTH
        $year = date("Y");
        $year = date("Y", mktime(0, 0, 0, $month)); // why +1 instead of +month+1?
        $monthTest = date("m", strtotime("+1 month"));
        $firstday = date("Y-M-d", mktime(0, 0, 0, $month, 1));
        $lastday = date("Y-M-t", mktime(0, 0, 0, $month, 1));
        if ($between == true) {
            $out = " ( $field BETWEEN '$firstday 00:00:00' AND '$lastday 23:59:59' ) ";
        } else {
            $out = " ( $field >= '$firstday 00:00:00' AND $field <= '$lastday 23:59:59' ) ";
        }
    } else {
        if ($between == true) {
            $variableMonthPlusOne = $monthPlusOne;

            $BeginningYearInQuestion = date("Y", strtotime("+$month month"));
            $BeginningMonthInQuestion = date("m", strtotime("+$month month"));

            $TestEndYearInQuestion = date("Y", strtotime("+$monthPlusOne month"));
            $TestEndMonthInQuestion = date("m", strtotime("+$monthPlusOne month"));
            $TestEndDayInQuestion = date("d", mktime(0, 0, 0, $TestEndMonthInQuestion,
                                         0, $TestEndYearInQuestion));

            // note: to get the last day of the month, go to the next month and use day "0"
            // OR simply use "t" instead of "d" in the date format

            $firstday = date("Y-$formatOut-d", mktime(0, 0, 0,
                             $BeginningMonthInQuestion, 1, $BeginningYearInQuestion));
            $lastday = date("Y-$formatOut-d", mktime(0, 0, 0,
                            $TestEndMonthInQuestion, 0, $TestEndYearInQuestion));
            $out = " ( $field BETWEEN '$firstday 00:00:00' AND '$lastday 23:59:59' ) ";
        } else {
            $variableMonthPlusOne = "+$monthPlusOne";
            $yearInQuestion = date("Y", strtotime("+$monthPlusOne month"));
            $lastday = mktime(0, 0, 0, $month, 0, $yearInQuestion);
            $out = " ( $field >= '"
                . date("Y-$formatOut-01", strtotime("$variableMonth month"))
                . " 00:00:00' and $field <= '"
                . date("Y-$formatOut-t", strtotime("+$variableMonth month"))
                . " 23:59:59' ) ";
        }
    }

    return($out);
}