AnsweredAssumed Answered

Problem in ExecuteSQL with date

Question asked by anubeazh on Jun 29, 2017
Latest reply on Jun 30, 2017 by fmpdude

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.

Outcomes