13 Replies Latest reply on Jun 30, 2017 6:04 AM by fmpdude

    Problem in ExecuteSQL with date

    anubeazh

      I'm making a commission calculation program for the HR. I'm using executeSQL to count the number of product sold then calculate the commission that Sales are going to get in each month. The commission periods are not always from the 1st day to the last day of the month so I can't just check the month's name instead. This is the script that I use:

       

      ExecuteSQL ( "SELECT COUNT (*)

      FROM EmployeeProduct_sale_count

      JOIN SerialUPC_sale_count ON SerialUPC_sale_count.pkSerial = EmployeeProduct_sale_count.fkSerial

      JOIN Product_sale_count ON SerialUPC_sale_count.fkUPC = Product_sale_count.pkUPC

       

      WHERE SaleDate  ≥ ? AND SaleDate  ≤ ? AND fkEmployeeID = ? AND CategoryName = ?"

       

      ; "" ; "" ; Dashboard::StartDate ; Dashboard::EndDate ; $empID ; "CPU" )

       

      It returns ? instead of a number. Dashboard::StartDate and Dashboard::EndDate are global fields. I tried using variables to store the data from the fields but it didn't work as well.

       

      The script works fine like this:

       

      ExecuteSQL ( "SELECT COUNT (*)

      FROM EmployeeProduct_sale_count

      JOIN SerialUPC_sale_count ON SerialUPC_sale_count.pkSerial = EmployeeProduct_sale_count.fkSerial

      JOIN Product_sale_count ON SerialUPC_sale_count.fkUPC = Product_sale_count.pkUPC

       

      WHERE fkEmployeeID = ? AND CategoryName = ?"

       

      ; "" ; "" ; $empID ; "CPU" )

       

      Does the date format has anything to do with this? In those date fields, the format are US format while my system is UK (aka the rest of the world ;P ) format.

       

      Thank you for the helps.