SQL parser error for DATE_ADD … INTERVAL

Archived from the Xataface Developers forum.

ADobkin — Sat Mar 31, 2012 12:48 pm

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 — Sat Mar 31, 2012 2:23 pm

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 — Sat Mar 31, 2012 2:57 pm

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 — Tue May 15, 2012 1:30 am

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