5 Replies Latest reply on Jul 9, 2013 9:37 AM by philmodjunk

    ExecuteSQL and dates

    gcatnine

      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

        • 1. Re: ExecuteSQL and dates
          MikhailEdoshin

               You should be able to solve this by combining the SQL requests:

               SELECT field FROM table WHERE date = (SELECT MAX(date) FROM table)

          • 2. Re: ExecuteSQL and dates
            philmodjunk

                 Does that really work? Others have reported that they can't "nest" one SELECT query inside another and get it to work with ExecuteSQL

            • 3. Re: ExecuteSQL and dates
              MikhailEdoshin

                   Hmm. Well, I myself try to stay away from v12 as far as possible, so I haven't tested it with v12 ExecuteSQL, but I've run it via plug-in interface in v11 and it did work. I've tested both the old and the new SQL functions and both gave the same result. The sample above has one glitch, however: the "date" must be in quotes. Here's what worked for me:

              SELECT number FROM test WHERE "date" = (SELECT MAX("date") FROM test)

              • 4. Re: ExecuteSQL and dates
                gcatnine

                     I test the solution proposed by Mikhail and it works (without quotes), except, it is 10% slower.

                     In any case, my point was about the different dates’ format in the same statement (inside ExecuteSQL or the returned data).

                • 5. Re: ExecuteSQL and dates
                  philmodjunk

                       Interesting as that contradicts what others have posted. Makes me wonder why it didn't work for them when it works here.

                       The data returned by ExecuteSQL is text (and SQL expects/produces a different format than FIleMaker dates entered via a FIleMaker layout). To work around this, you'll need to use a calculation to extract the month, day and year values in order to "recast" them as a FIleMaker date.

                       It's FileMaker Inc's call as to whether or not this is considered a "bug" or not...