AnsweredAssumed Answered

datevalue + ? in ExecuteSQL() results YYYYMMDD without separator

Question asked by user19752 on Mar 23, 2016
Latest reply on Mar 28, 2016 by beverly

For example, d as a date type field, (n is number for decreasing resultset, not have so meaning)

 

ExecuteSQL ( "SELECT d + ? FROM data WHERE n=1" ; "" ; "" ; 1 )

or

ExecuteSQL ( "SELECT d + ? FROM data WHERE n=1" ; "" ; "" ; "1" )

result:20160324

 

Using constant instead of field results same, so field definition not affect the result.

ExecuteSQL ( "SELECT DATEVAL('2016-03-23') + ? FROM data WHERE n=1" ; "" ; "" ; 1 )

 

Using cast corrects result.

ExecuteSQL ( "SELECT d + NUMVAL(?) FROM data WHERE n=1" ; "" ; "" ; 1 )

or

ExecuteSQL ( "SELECT d + STRVAL(?) FROM data WHERE n=1" ; "" ; "" ; 1 )

or

ExecuteSQL ( "SELECT DATEVAL(d + ?) FROM data WHERE n=1" ; "" ; "" ; 1 )

result:2016-03-24

 

But 8 digit number itself is not correct literal date format

ExecuteSQL ( "SELECT DATEVAL('20160324') FROM data WHERE n=1" ; "" ; "" ; 1 )

result:?

 

So it seems resulted date may be converted to the format when serializing result set to text, but I can't get why only occurs using parameter.

 

Tested on FM12/14 on Windows7.

Outcomes