9 Replies Latest reply on Oct 31, 2012 3:19 PM by PaulWebb

    ExecuteSQL between dates

    PaulWebb

      I have the following that is working to give me a list of dates (notice first part of WHERE is commented out). I am trying to change it so it gives me a list of records between two dates.

       

      SR_Create_Date - calculation field to pull the date from a timestamp field using GetAsDate ( text )

      $startdate - (Get ( CurrentDate) - DayOfWeek (Get ( CurrentDate )) - 6 )

      $enddate - Get ( CurrentDate ) - DayOfWeek (Get (CurrentDate ))

       

      I used SeedCodes SQL Eplorer to get as far as I did below. I've tried everything I could think of or find online. Need some direction on this one.

       

      Thanks for looking.

       

      // ------------ BEGIN EXECUTESQL BLOCK ------------

      Let ( [

      ReturnSub = " " ; // We need to swap out carriage returns in your results with a different character, so specify the character here.   is the default.

      SQLResult = ExecuteSQL (

      // ------------ BEGIN QUERY ------------

      "SELECT a."SR_Create_Date"

      FROM "SR" a

      WHERE /* a."SR_Create_Date" >= '$startdate' AND */ a."SR_Create_Date" <= '$enddate'

      ORDER BY a."SR_Create_Date" ASC" ;

      // ------------ END QUERY ------ ------

      // ------------ BEGIN FIELD AND ROW SEPARATORS ------------

      " " ; "|*|"

      // ------------ END FIELD AND ROW SEPARATORS ------------

      ) ] ;

      // ------------ BEGIN CARIAGE RETURN SUBSTITUTIONS ------------

      Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] )

      // ------------ END CARIAGE RETURN SUBSTITUTIONS ------------

      )

      // ------------ END EXECUTESQL BLOCK ------------

        • 1. Re: ExecuteSQL between dates
          beverly

          WHERE a.\"SR_Create_Date\" BETWEEN '" & $startdate & "' AND '" & $enddate & "'

           

          • the '$startdate' & '$enddate' become LITERAL strings.

          • the BETWEEN ... AND ... are valid in the WHERE clause

           

          Beverly

           

           

          WHERE  /* a.\"SR_Create_Date\" >= '$startdate' AND */ a.\"SR_Create_Date\" <= '$enddate'

           

          • 2. Re: ExecuteSQL between dates
            PaulWebb

            Hi Beverly ... I was just reading one of your articles trying to figure this out. http://www.filemakerhacks.com/?p=6406

             

            I tried your suggestion but the result was no records. And yes there are records in that date range. Is something off a bit?

            • 3. Re: ExecuteSQL between dates
              PaulWebb

              The problem seems to be something with the date.

               

              I turned around my original code to...

              WHERE  a.\"SR_Create_Date\" >= '$startdate'

               

              Which returns nothing. The variable I bumped from -6 to -30 which equates to 9/27/2012 and I have records all the way up to 10/28. I then changed the code from >= to <= and received records. All of the reords. So records on both sides of the variable date.

              • 4. Re: ExecuteSQL between dates
                beverly

                Hi Paul. I took it out of the query and made the dates into arguments.

                 

                Let (
                   [ $startdate = (Get ( CurrentDate) - DayOfWeek (Get ( CurrentDate )) - 6 )
                   ; $enddate = Get ( CurrentDate ) - DayOfWeek (Get (CurrentDate ))
                   
                   ; $query = " SELECT a.\"SR_Create_Date\"
                      FROM \"SR\" a
                      WHERE a.\"SR_Create_Date\" BETWEEN ? AND ?
                      ORDER BY a.\"SR_Create_Date\" "
                   
                   ; $result = ExecuteSQL ( $query
                      ; "    " ; "" 
                      ; $startdate ; $enddate
                      )
                      
                   ]; $result
                )
                

                 

                Beverly

                • 5. Re: ExecuteSQL between dates
                  gwinzeler

                  are there any null values in date field?

                  I have found that can cause problems sometimes so you can add ---

                  WHERE SR_Create_Date is not null and SR_Create_Date between ? and ?

                   

                  sorry didn't add escapes so easier to read

                  GARY

                  • 6. Re: ExecuteSQL between dates
                    PaulWebb

                    That did it, thanks!

                     

                    Now with most of executesql fields I've built (all 5) I have been able to change it to a count by...

                    SELECT COUNT (a.\"SR_Create_Date\")

                     

                    That is not working here. Is it because I'm using a date range? 

                    They were right when they said executesql was powerful! It is giving me a powerful headache. What am I missing Obi-Wan?

                    • 7. Re: ExecuteSQL between dates
                      PaulWebb

                      no null values. data will be coming from another DB and there should never be any null values either, hopefully!

                      • 8. Re: ExecuteSQL between dates
                        beverly

                        Do you have other fields/columns in the SELECT clause? if so, you need to include them in an GROUP BY clause.

                         

                        Beverly

                        • 9. Re: ExecuteSQL between dates
                          PaulWebb

                          Go it! I was deleting the dbl quotes at the end of the query. Thanks again.

                           

                          If you know of any training material I'd appreciate it.

                          Paul