4 Replies Latest reply on Apr 25, 2017 3:06 PM by greglane

    Date Format In SQL

    Joshua_1

      Hi,

       

      I am trying to retrieve sales data for the last 90 days but I'm running into formatting issues. SQL's format is YYYY-MM-DD while Get(CurrentDate) is MM-DD-YYYY. My SQL statement is as follows:

       

      ExecuteSQL("SELECT SUM(subTotal) FROM Orders WHERE CustomerID = ? AND OrderDate > ?"; ""; ""; Customers::CustomerID; Get(CurrentDate) - 90)

       

      but because the formatting are different... it's not working. What is the simplest correct way that I should be executing this SQL statement?

       

      I have:

      Filemaker Pro 13 Advanced

      Windows 7

       

      Thanks In Advance!

        • 1. Re: Date Format In SQL
          beverly

          Try this:

          GetAsDate (Get(CurrentDate) - 90))

           

           

          Sent from miPhone

          1 of 1 people found this helpful
          • 2. Re: Date Format In SQL
            Joshua_1

            Thank you for your response beverly but unfortunately it did not work. I finally got it working with...

             

            Let([

             

            $NinetyDaysAgo = Get ( CurrentDate ) - 90;

            $NinetyDaysAgoSQL = Year ( $NinetyDaysAgo) & "-" & Case(Month ( $NinetyDaysAgo) < 10; "0") & Month ( $NinetyDaysAgo) & "-" & Case(Day ( $NinetyDaysAgo ) < 10; "0") & Day ( $NinetyDaysAgo )];

             

            ExecuteSQL("SELECT SUM(SubTotal) FROM Orders WHERE ID = ? AND PurchaseDate > ?"; ""; ""; Customer::ID;  $NinetyDaysAgoSQL)

            )

             

             

            I looks ugly, so, if anyone can provide me with the correct way of doing this I'd greatly appreciate it.

            • 3. Re: Date Format In SQL
              fmpdude

              Date handling in FMP is inconsistent and can be both very frustrating and needlessly time consuming. SQL is a prime example of just that. Hopefully, dates at some point will be consistent throughout the product!

               

              ------

               

              In any case, below is a Custom Function you can try. I forget where I got it, but it was here on the forum. The magic is in the "+" character.

               

              // parameter: UnixTimeStamp

               

              Let ( [

              theSQLTS = UnixTimeStamp ;

              theFMTS = Substitute ( theSQLTS ; "-" ; "+" )

              ] ;

              GetAsTimestamp ( theFMTS )

              )

               

              --

               

              I would also recommend you invest in a SQL utility, like RazorSQL, that can connect to FMP and do regular SQL statements without all the ugliness of ExecuteSQL and the infernal and totally unhelpful "?". Once you get the query working, then move it into FMP.

               

              HOPE THIS HELPS.

              • 4. Re: Date Format In SQL
                greglane

                Your original statement should work if OrderDate is a date field. I noticed in your revised statement you’re using PurchaseDate. Are OrderDate and PurchaseDate both date fields in your Orders table?