My tests on a Mac to return a DATE with addition (or subtraction) in ExecuteSQL():
works where ? is a number = date result
works with the number added = date result
works as is using constant (number) = date result
So when using the parameter, the DateVal() or STRVAL() will help cast as the date result, instead of returning the NUMBER result. If you use the constant, the casting is not needed.
= number result.
But number is different from YYYYMMDD.
NUMVAL(d + ?)
d + DATEVAL(?) //"invalid" calculation, date can't be added to date. FM may convert both to number before add.
This is count of days from 0001-01-01.
Yes, I'm just reporting the tests and results I got. Some of my syntax was different, so perhaps that helps narrow down what's happening. Or perhaps window and macintosh have different result. This is worthy further exploration. I hope others jump in with various testing!
1 + 'a'
1 + NUMVAL('a')
silent conversion do it as FileMakish way, like GetAsNumber('a'), so NUMVAL() or ...VAL() functions are not usable for getting same result.
So, your "number result" may be the correct reading. It may be calculated like as
GetAsNumber ( GetAsText ( d ) ) + GetAsNumber ( ? )
If so, I think this is bug since "date + number" should be defined operation, don't need to be converted as "number + number" (and date can be converted directly to number but this is formatted as text once before convert).
Similar problem, use timestamp instead of date
ts + ?
Lost date part. Casting corrects it
ts + NUMVAL(?)
result: 2016-03-28 00:00:01 (?=1 is added as seconds)
Adding date to timestamp is not valid, but interesting.
ts + DATEVAL(?)
date part is added to hours. But the result is where NUMVAL(DATEVAL(ts)) *24 is 17665224 and ?=1...
I noticed the parameter in "date + ?" can be both "number (of days)" or "time (to make timestamp result)".
great testing! The issue seems to be when the addition (or subtraction) of a number to date or timestamp is in the parameter format (?) rather than the constant. Then the number needs to be cast, or as I tested, the entire column needs to be cast.
very interesting that time can be added to the date.