1 Reply Latest reply on Aug 15, 2015 11:25 AM by philmodjunk

    Attempting to set a date variable using ExecuteSQL

    Stu412

      Title

      Attempting to set a date variable using ExecuteSQL

      Post

      I'm looking to use a ExecuteSQL statement to select a date from a date field, place it in a variable and use it elsewhere.

      The 'where' criteria are quite complex, hence the use of SQL.  Basically, I've got:

      Set Variable [ExecuteSQL "Select Date from DataTable where...criteria1....criteria2 etc."]

      I think the problem is with the use of SQL and date formats, but I'm not sure how to simply get 31/01/2015 from table A to table B, so to speak.

      Thanks

       

        • 1. Re: Attempting to set a date variable using ExecuteSQL
          philmodjunk

          The date is still a FileMaker date field that you are Selecting. If your calculation field specifies a date result type, you should get either a date, null or a ? result. Null means no record matching the specified criteria was found and a ? of course means that you have a syntax error in your query. In cases where you are not using this in a calculation field, you might need to enclose the results in a GetAsDate function to make sure that the value is not returned as text.

          The place where dates cause problems in ExecuteSQL are when you need to specify a literal date in the expression. Then you have to follow the rules for using a SQL compatible date format.