0

It prints: The syntax of the string representation of a datetime value is incorrect without a reason, when I try to call a stored proc with DATE parameters from another stored proc.

Strange thing, presence or absence of unused variables affects the result.

this fails:

--<ScriptOptions statementTerminator="^"/>
CREATE PROCEDURE MYTESTDATE2 ()
    DYNAMIC RESULT SETS 1
P1: BEGIN ATOMIC
    DECLARE @FOO INTEGER;

    CALL MYTESTDATE1 (
'2014-07-30'                                    -- IN @DATE_OF_INSERT TIMESTAMP,             
);

END P1
^

This succeeds:

CREATE PROCEDURE MYTESTDATE2 ()
    DYNAMIC RESULT SETS 1
P1: BEGIN ATOMIC
    DECLARE @FOO INTEGER;

    SET @FOO = NULL;

    CALL MYTESTDATE1 (
'2014-07-30'                                    -- IN @DATE_OF_INSERT TIMESTAMP,             
);

END P1

This succeeds too:

CREATE PROCEDURE MYTESTDATE2 ()
    DYNAMIC RESULT SETS 1
P1: BEGIN ATOMIC
    CALL MYTESTDATE1 (
'2014-07-30'                                    -- IN @DATE_OF_INSERT TIMESTAMP,             
);

END P1
basin
  • 3,949
  • 2
  • 27
  • 63
  • Why are you supplying a `DATE` if the parameter is defined as `TIMESTAMP`? – mustaccio Jul 31 '14 at 12:54
  • Why not? Shouldn't it implicitly convert it to TIMESTAMP? – basin Jul 31 '14 at 12:58
  • Where does it say that it should? – mustaccio Jul 31 '14 at 13:53
  • @mustaccio - well, the documentation says that [`DATE` will be promoted to `TIMESTAMP`](http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008477.html?cp=SSEPGG_9.7.0%2F2-10-2-3-1&lang=en), so looks like it does. iSeries versions don't let you _compare_ them, for whatever reason, but the assignments still auto-promote. DB2 considers an ISO formatted string involiate, though, so I'd never expect it to complain about that format (if you pass in an actual cast `DATE` it shouldn't complain), so something else is going on. – Clockwork-Muse Aug 01 '14 at 10:53

0 Answers0