1 of 1 people found this helpful
GetAsDate (Get(CurrentDate) - 90))
Sent from miPhone
Thank you for your response beverly but unfortunately it did not work. I finally got it working with...
$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.
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.
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?