6 Replies Latest reply on Mar 28, 2016 3:36 AM by beverly

    datevalue + ? in ExecuteSQL() results YYYYMMDD without separator

    user19752

      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.