Topic
  • 3 replies
  • Latest Post - ‏2013-12-19T08:00:12Z by MatthiasNicola
Guruashrit
Guruashrit
2 Posts

Pinned topic xml datetime casting to sql date in xmlexists cluase

‏2013-11-07T12:41:31Z |

Hi,

xml has datetime field, that need to be compared with date coming from stored procedure (normal date). I need to compare xml timestamp filed with sql date. Getting error for not proper casting.

Query:

Select orderitem

From Order

where xmlexists('$d/*:order[xs:date(orderDate)=xs:date(IN_TRIP_CREATION_DT)]' passing ORDER_DA as "d");

The orderDate filed is of the format 2013-12-05T00:00:00.000  and IN_TRIP_CREATION_DT is input from sp of the format 2013-12-05.

We need to use only date /xmldate format , Varchar conversion is not allowed nor the XMLTABLE usage.

Please help me to solve this issue.

Regards,

Gururaj Ashrit 

  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: xml datetime casting to sql date in xmlexists cluase

    ‏2013-11-25T07:57:13Z  

    Hi Gururaj,

    sorry for the delay in responding.

    Please post the exact DB2 error message and error code that you have received. Otherwise it is hard to guess exactly which error DB2 is complaining about.

    Also, is the SQL statement in your post above the exact statement in your stored procedure?  For example, if IN_TRIP_CREATION_DT is passed in from the SP, then IN_TRIP_CREATION_DT should also appear in the PASSING clause of the XMLEXISTS function. Also, in XPath expression itself the variable $IN_TRIP_CREATION_DT would need to start with a $-sign.

     

    Thanks,

     

    - Matthias

  • Guruashrit
    Guruashrit
    2 Posts

    Re: xml datetime casting to sql date in xmlexists cluase

    ‏2013-12-17T12:26:01Z  

    Hi Gururaj,

    sorry for the delay in responding.

    Please post the exact DB2 error message and error code that you have received. Otherwise it is hard to guess exactly which error DB2 is complaining about.

    Also, is the SQL statement in your post above the exact statement in your stored procedure?  For example, if IN_TRIP_CREATION_DT is passed in from the SP, then IN_TRIP_CREATION_DT should also appear in the PASSING clause of the XMLEXISTS function. Also, in XPath expression itself the variable $IN_TRIP_CREATION_DT would need to start with a $-sign.

     

    Thanks,

     

    - Matthias

    Hi Matthias,

    Sorry for half inputs.

    Error  : The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007, DRIVER=4.13.111

    I am constructing dynamic stored procedure, below is the exact code.

    Input is IN IN_TRIP_CREATION_DT DATE.

     

    SETV_SQL_WHERE = V_SQL_WHERE ||

    ' and xmlexists(''$d/*:order[xs:date(orderDate)=xs:date($CREATION_DT)]'' passing ORDER_DA as "d",'''||IN_TRIP_CREATION_DT||''' as "CREATION_DT")';

     

    Thanks,

    Gururaj Ashrit

  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: xml datetime casting to sql date in xmlexists cluase

    ‏2013-12-19T08:00:12Z  

    Hi Matthias,

    Sorry for half inputs.

    Error  : The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007, DRIVER=4.13.111

    I am constructing dynamic stored procedure, below is the exact code.

    Input is IN IN_TRIP_CREATION_DT DATE.

     

    SETV_SQL_WHERE = V_SQL_WHERE ||

    ' and xmlexists(''$d/*:order[xs:date(orderDate)=xs:date($CREATION_DT)]'' passing ORDER_DA as "d",'''||IN_TRIP_CREATION_DT||''' as "CREATION_DT")';

     

    Thanks,

    Gururaj Ashrit

    Hi Gururaj,

     

    thanks for the details.

    The error msg claims that the date value that is passed in as $CREATION_DT is not a valid date value, i.e. that the format of the date value is incorrect.

    I understand that you are constructing a SQL/XML statement as a string which later gets prepared and executed. When you create the string V_SQL_WHERE as shown in your post above, the value of IN_TRIP_CREATION_DT becomes a simple piece of string without a data type such as DATE. To be more precise, the string concatenation (||) converts the input type of IN_TRIP_CREATION_DT into a string.  

    I suspect you can avoid the resulting error message by explicitly casting the value of IN_TRIP_CREATION_DT into a date again, similar to this:

    passing ORDER_DA as "d", cast(  '''||IN_TRIP_CREATION_DT||'''  AS DATE) AS  "CREATION_DT")

     

    Does this work for you?

     

    Thanks,

    Matthias