SQL parser error for DATE_ADD ... INTERVAL

[ADobkin]

I am trying to create a grafted field in my __SQL__ directive in fields.ini to determine the "week ending" date. The query snippet below works fine in MySQL to display the date of Friday for any given week, but I am getting the following error:

The Error was Parse error: Expected an expression and unit for the interval on line 1 SELECT t.*, WEEK(myDate,5) AS weekNum, DATE_ADD(myDate, INTERVAL (6-DAYOFWEEK(myDate)) DAY) AS weekEnd ...

Any suggestions? Is this a parser bug, or is there another way I should write this query?

Thanks,
Alan

[ADobkin]

I was able to solve part of the problem by doing the calculation in PHP rather than SQL. Here is the function in my table delegate class:

Code: Select all

function weekEnd__display(&$record) {
        $myDate = $record->strval('myDate');
        $sunTS = strtotime("Sunday", strtotime($myDate));
        $friTS = $sunTS - 60*60*24*2;
        return date("Y-m-d", $friTS);
}

This displays the correct date in the field, however I need to filter the list view on this value (using filter = 1 in fields.ini). The filter selection list ignores the display function, so it just displays the raw values from the database.

[ADobkin]

Okay, I had a little more caffiene and figured out another solution that works perfectly!

I am now using the following query syntax in my __SQL__ directive:

Code: Select all

STR_TO_DATE(CONCAT(DATE_FORMAT(myDate, '%X%V'), ' Friday'), '%X%V %W') AS weekEnding

No need for any PHP in this case, and the filter works as expected. Hopefully this will help someone else.

[ADobkin]

Note: Refer to this forum post for a possible solution to the original problem:

Calculated Field in Relationship

I have not tested it in this case yet....

Alan