1 of 1 people found this helpful
You need to wrap your date in GetAsDate() , because ExecuteSQL doesn't understand friendly USA date formats. I'm not quite sure what tests you tried in your 2nd-to-last paragraph, but I think that only one half of your OR query was working.
ExecuteSQL ( "SELECT SUM (Hours_Project) FROM Tm_ProjectHours WHERE Job = ? AND WorkDate =?" ; "" ; ""; 206950; GetAsDate("6/14/2013"))
I think it should work.
I appreciate the suggestion. I tried the GetAsDate("6/14/2013") with and without the quotes, and both continue to evaluate as NULL.
But it really helped to take another look, as I discovered the correct year should have been 2012 not 2013.
Three hours I won't get back. Thanks again.
This might come in handy for you ... it is a custom function from Kevin Frank. It NORMALIZES your dates. I hope it's helpful.
/* Purpose: converts a FileMaker date to a SQL-friendly date
Author: Kevin Frank w/ help from Koen Van Hulle
Note 1: this CF handles US-style (MM/DD/YYYY) and European-style (DD/MM/YYYY) date formats,
as well as the date returned by a SQL SELECT statement ('YYYY-MM-DD')
Note 2: this CF works with all versions of FileMaker >= 8.0
Usage: DateToSQL ( pDate )
Example 1: DateToSQL ( Get ( CurrentDate ) ) =
DATE '2010-05-25' ...assuming today's date is 25 May 2010
Example 2: DateToSQL ( $sqlDate ) =
DATE '2010-12-31' ...assuming $sqlDate = '2010-12-31' or 2010-12-31 (i.e., with or without the single quotes)
Let ( a = Substitute ( pDate ; "'" ; "" ) ; // strip off single quotes, if any, in case this is a SQL-style date
If ( Middle ( a ; 5 ; 1 ) = "-" and Middle ( a ; 8 ; 1 ) = "-" ;
"DATE '" & a & "'" ;
Let ( [
m = Right ( "00" & Month ( pDate ) ; 2 ) ;
d = Right ( "00" & Day ( pDate ) ; 2 ) ;
y = Year ( pDate )
"DATE '" & y & "-" & m & "-" & d & "'"
) // end let
) // end if
) // end let