How can we use functions like Month() or Day() in "WHERE" statement of SQL?
"SELECT Sum( myTotal )
WHERE Month(CreationDate) >= ?";
Any advise is appreciated.
Usually there are diffrent years in date field then comapring only month doesn't get good result.
And using Month(date) (or any calculation) in where make it slow since index can't be used.
with parameter value Date($month;1;$year) would be better.
(But FM may have month index on date field)
What "error" are you getting.
1. place EvaluationError() around the ExecuteSQL()
or if you have FMPro Advanced,
2. put the query in to Data Viewer and see what the error message may be.
Month() is allowed.
Hi all, sorry for replying late.
Here is the simple SQL query:
ExecuteSQL (" Select Total FROM Orders WHERE "& Month(Orders::DateOrdered) & "=?";"" ; ¶ ; $month)
This would return some data if $Month is set to 2, and returns blank on all other month. getasnumber($month) does not make any improvements.
So I ended up tweaking user19752 solution as below:
Let ( [
~month = Month ( Get(CurrentDate));
~nextMonth = Month ( Get(CurrentDate))+1;
~year = Year( Get(CurrentDate));
~result =ExecuteSQL (" Select DateOrdered FROM Orders WHERE DateOrdered >=? and DateOrdered <=?"
Retrieving data ...