AnsweredAssumed Answered

ExecuteSQL and dates

Question asked by gcatnine on Jul 4, 2013
Latest reply on Jul 9, 2013 by philmodjunk

Summary

ExecuteSQL and dates

Product

FileMaker Pro

Version

12v3 and 4

Operating system version

Windows

Description of the issue

I would like to run the following calculation:
ExecuteSQL("SELECT Field FROM Table WHERE Date = ?" ; "" ; ""; ExecuteSQL (" SELECT MAX (Date) FROM Table ";"";""))

But unfortunately it does not work because the second SQL query returns the date in the format yyyy-mm-dd and the Date in the WHERE clause is in the format dd-mm-yyyy.

So it has no sense to have two different date formats in the SQL statement

Steps to reproduce the problem

ExecuteSQL("SELECT Field FROM Table WHERE Date = ?" ; "" ; ""; ExecuteSQL (" SELECT MAX (Date) FROM Table ";"";""))

Expected result

works

Actual result

does not work

Exact text of any error message(s) that appear

NA

Configuration information

windows 7
FM 12v3 and v4

Workaround

change the format of the comparison date:

let ([
max_d =  ExecuteSQL (" SELECT MAX (Date) FROM Table ";"";"");
max_d2 = Right(max_d;2) & "-" & Middle( max_d; 6; 2) & "-" & Left(max_d;4);
return_field = ExecuteSQL("SELECT Field FROM Table WHERE Date = ?" ; "" ; ""; max_d2) ];
return_field)

Now, changing the format of the comparison date it works

Outcomes