Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    13

    Unanswered: Column Mapping Resultset DATE TIME

    Hello,


    I'm mapping columns of types DATE(10), TIME(8):

    Code:
    <parameter property="ORGDEPDT" jdbcType="DATE" javaType="java.lang.String" mode="OUT"/>
    
    <parameter property="ORGDEPTM" jdbcType="TIME"
    javaType="java.lang.String" mode="OUT"/>
    
    <parameter property="DSTDEPDT" jdbcType="DATE" javaType="java.lang.String" mode="OUT"/>
    
    <parameter property="DSTDEPTM" jdbcType="TIME" javaType="java.lang.String" mode="OUT"/>

    and i get Exception:

    javax.servlet.ServletException:
    --- The error occurred in com/linehaul/persistence/sql/LineHaulManifests.xml.
    --- The error occurred while applying a parameter map.
    --- Check the ApplicationSP.Params.
    --- Check the statement (update procedure failed).
    --- Cause: java.sql.SQLException: [SQL0180] Syntax of date, time, or timestamp value not valid. Cause . . . . . : The string representation of a date, time, or timestamp value does not conform to the syntax for the specified or implied data type and format. *N is either the character string constant that is not valid or the column or host variable that contained the string. If the name is *N, then the value is an expression specified in the statement. If the string was found in a host variable, the host variable number is 9. Recovery . . . : Ensure that the date, time, or timestamp value conforms to the syntax for the data type it represents. Try the request again.
    org.apache.struts.action.RequestProcessor.processE xception(RequestProcessor.java:535)


    Any useful hint for solving this?


    Regards

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure I understanding what you are doing in your program, but for inserting a date, you should use a string in the following format:

    '2008-11-30'

    There are other acceptable formats depending on the territory code of your server, but the above ISO format should always be used for inserts because it accepted for all territory codes.

    The ISO format for time is:

    '16.48.42'
    Last edited by Marcus_A; 11-30-08 at 17:49.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2008
    Posts
    13
    Thank you for the answer, Marcus A!


    So, I have next values in DB:
    Date
    30/11/08
    Time
    16:48:42

    I need to get them out of there. Mentioned mapping doesn't work.
    I tried also with java.sql.Date, java.sql.Time
    I inserted the values via query browser in format '2008-11-30', but when i do a SELECT i get the mentioned other format.

    What changes do i need to make and where? This format differs from standard ISO format.


    Regards

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Data is always stored in a single format inside DB2 for date, time, timestamp, (this is in an internal numeric format that is not a string) but it is returned to your client in a format depending on the territory code of the client.

    To change the format when it is returned to your client, use the CHAR function:

    Select CHAR(current_date, ISO) from sysibm.sysdummy1;

    Please refer to SQL Reference Vol 1 for other format options on the CHAR function as they apply to date, time, timestamp.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •