8 Replies Latest reply on May 7, 2016 11:11 AM by erolst

    Using sql to get minimum date

    Diver

      I am trying to get the minimum/earliest date found in all records of a table but am having no luck.  The field is a simple date field and my sql is looking like:

       

      ExecuteSQL (

      "SELECT MIN(a.\"dateEntered\")

      FROM \"myTable\" a

      WHERE a.\"dateEntered\" > ?" ;

      "    " ; "|*|" ;

      "5/1/16"

      )

       

      The result returns a number: 1126

       

       

      I can get the number of records found with no problem using:

       

      ExecuteSQL (

      "SELECT COUNT(a.\"dateEntered\")

      FROM \"myTable\" a

      WHERE a.\"dateEntered\" > ?" ;

      "    " ; "|*|" ;

      "5/1/16"

      )

       

      I am only guessing it might have to do with how sql uses dates versus FileMaker.  Any help would be greatly appreciated!

        • 1. Re: Using sql to get minimum date
          erolst

          Use the Date() function to pass a correctly constructed date:

           

          ExecuteSQL ( "

            SELECT COUNT ( dateEntered )

            FROM myTable

            WHERE dateEntered > ?

            " ; "    " ; "|*|" ; Date ( 5 ; 1 ; 2016 )

          )

          • 2. Re: Using sql to get minimum date
            Diver

            Thanks for the reply erolst, I have tried the following but still just get a number.

            *Note your sample still had COUNT so I changed it to MIN

             

            ExecuteSQL ( "

              SELECT MIN ( dateEntered )

              FROM myTable

              WHERE dateEntered > ?

              " ; "    " ; "|*|" ; Date ( 5 ; 1 ; 2016 )

            )

            • 3. Re: Using sql to get minimum date
              electon

              "SELECT theDate FROM myTable ORDER BY theDate FETCH FIRST ROW ONLY"

               

              This should return one result when sorted by theDate Ascending.

              • 4. Re: Using sql to get minimum date
                erolst

                Diver wrote:

                *Note your sample still had COUNT so I changed it to MIN

                 

                ExecuteSQL ( "

                SELECT MIN ( dateEntered )

                FROM myTable

                WHERE dateEntered > ?

                " ; " " ; "|*|" ; Date ( 5 ; 1 ; 2016 )

                )

                Yes, of course; I accidentally took the wrong code block …

                 

                Diver wrote:

                but still just get a number.

                Diver wrote:

                The field is a simple date field

                Regarding the former, are you sure about the latter? Is it maybe a number field after all …?

                 

                Screen Shot 2016-05-07 at 18.27.54.png

                 

                Don let the date format in the sample data confuse you; it is (European ordered) DD/MM/YYYY.

                • 5. Re: Using sql to get minimum date
                  electon

                  Ah, the topic didn't clearly state you want first date after a specific date.

                  Now I can see that in the calculation.

                   

                  What erolst posted should give you the date, not a number.

                  Is this within a script variable or a calculation field?

                  If it's a field and the result is set to return result as number then change it to text.

                  • 6. Re: Using sql to get minimum date
                    Diver

                    Thanks for the screen shot as I could see your test works.

                     

                    I created a new sample database and did as you did and it worked.  I then used it on my live site and it still failed.   I then tried using a different date field from my database and it worked!

                     

                    I have confirmed that the date field originally used was indeed a date field that was indexed.  It did have a validation on it but that should not matter as it only validates during data entry.

                     

                    I can only guess that there is some kind of corruption on the original date field 'dateEntered' or its data as it works with another date field.

                     

                    Thanks for the help!

                    • 7. Re: Using sql to get minimum date
                      Diver

                      Thanks for the added support election!

                      • 8. Re: Using sql to get minimum date
                        erolst

                        You're welcome; glad I could help.