SQL parser error for DATE_ADD ... INTERVAL

A place to discuss development of the Xataface core.

SQL parser error for DATE_ADD ... INTERVAL

Postby 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
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: SQL parser error for DATE_ADD ... INTERVAL

Postby 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
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

SQL Query for Week Ending - Solved!

Postby 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
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: SQL parser error for DATE_ADD ... INTERVAL

Postby 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
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA


Return to Xataface Developers

Who is online

Users browsing this forum: No registered users and 1 guest

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved