6 Replies Latest reply on Apr 28, 2014 8:38 AM by deathrobot

    ExecuteSQL with date parameter

    thurmes

      Title

      ExecuteSQL with date parameter

      Post

           I would like to use ExecuteSQL to retrieve data for me, but need to filter the data to be after a given date, which I'd like to give in a parameter. For some reason,

           this works:

           ExecuteSQL ( "
           SELECT u.BookValue 
           FROM UseMixtures u 
           WHERE u.DateUsed > DATE '2013-09-30' " ;
            "" ; ""  )
           (but the date isn't in parameter form)
            
           and this doesn't:
                ExecuteSQL ( "
                SELECT u.BookValue 
                FROM UseMixtures u 
                WHERE u.DateUsed > ? " ;
                 "" ; "" ;
                "DATE '2013-09-30'" )
                 
                Can anyone tell me where I went wrong? Eventually the parameter will be called from elsewhere but formatted correctly, but this simplified system should work (shouldn't it?). The result for the former is a list of values; the result for the latter is nothing (i.e., not a ?, as often shows up when I try to use ExecuteSQL).
                Thanks for any help.
                --Bill

            

        • 1. Re: ExecuteSQL with date parameter
          saigopaldas

               Try using this,

               ExecuteSQL (
                
               "SELECT u.BookValueu.SalesOrder
                
               FROM UseMixtures u
                
               WHERE u.DateUsed> ? " ;
                
                "" ; "" ;
                
               "09/30/2013" )

               This should work.

          • 2. Re: ExecuteSQL with date parameter
            philmodjunk

                 The parameter's value should not be expressed in SQL's date syntax, but in FileMaker's date syntax. Haven't tried literal text so it might work. If not, try:

                 "" ; DateField )

                 or

                 "" ; Date ( 9 ; 30 ; 2013 )

                 or

                 "" ; getasdate ( "9/20/2013" )

            • 3. Re: ExecuteSQL with date parameter
              thurmes

                   Thanks, Saigopal & PhilModJunk.

                   Both answers worked; perhaps PhilModJunk's works a bit better because it will always be correct no matter what the system's preferred date format is.

                   I hadn't realized that the parameters needed to be in FileMaker's syntax. That should be the last piece of the puzzle, so that I can use the more complicated filter that gives me the exact answer (I pared this problem down to the bare minimum so it would be easier to understand).

                   Thanks again,

                   --Bill

              • 4. Re: ExecuteSQL with date parameter
                philmodjunk

                     The first two examples are independent of system date settings, but I believe that GetAsDate is not.

                • 5. Re: ExecuteSQL with date parameter
                  thurmes

                       I got an email from Michael L. today (see following) which was a comment on this (but not posted here?); I'll answer it here:

                       Found this post as I am trying to do the exact same thing. However, I keep getting a question mark. Am I missing something here?

                       ExecuteSQL (

                       "SELECT id

                       FROM QuickLeads

                       WHERE date > ? "; "" ; "" ;

                       Date ( 12 ; 31 ; 2013 )

                       )

                       -----

                       Michael, your context is OK, but date is one of those SQL restricted words (check out https://drupal.org/node/141051). So change

                       date   to   \"date\"  and it should work fine.

                        

                       --Bill

                  • 6. Re: ExecuteSQL with date parameter
                    deathrobot

                         Thanks, William. I realized "date" was a reserved word after I posted my question, and so deleted it. Sorry you still got the email. However, my solution was to change the field name. I didn't realize I could just add the \" on either side. Thanks for the tip!

                         Michael