Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2013
    Posts
    11

    Unanswered: DB2 date conversion

    //Hi All Please find the code and please give me reply as soon as possible. i am inserting the Date format in string format i.e.'1/20/2014 12:00:00 AM'. but i am getting error


    CREATE or Replace FUNCTION TestPmcDate (timestamp_in VARCHAR(30))
    RETURNS TIMESTAMP
    LANGUAGE SQL
    Contains SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    Begin Atomic


    if timestamp_in ='MM-DD-YYYY HH24:MIS AM' Then
    --TIMESTAMP(yyyy||mm||dd||hh||mi||ss);
    VALUES( SUBSTR(timestamp_in, 1, 2),
    SUBSTR(timestamp_in, 4, 2),
    SUBSTR(timestamp_in, 7, 4),
    SUBSTR(timestamp_in, 12, 2),
    SUBSTR(timestamp_in, 15, 2),
    SUBSTR(timestamp_in, 18, 2),
    SUBSTR(timestamp_in, 21, 2));
    End if;
    Return timestamp_in;
    END


    //Error

    ------------------------------ Commands Entered ------------------------------
    select TestPmcDate('1/20/2014 12:00:00 AM')from sysibm.sysDummy1@
    ------------------------------------------------------------------------------
    select TestPmcDate('1/20/2014 12:00:00 AM')from sysibm.sysDummy1

    1
    --------------------------
    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    ..and please give me reply as soon as possible..
    if you would take the time to read some docs or check/analyze the message..
    this is not the wikipedia of DB2...
    nice of you to indicate the "please"
    in the infocenter, there are chapters talking about this, PLEASE take to time to read
    Date and time formats by territory code - IBM DB2 9.7 for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I felt that responding to dharmendra87(op of this and some other thread) might be useless.

    (1) I couldn't see any effort of him(her) to make codes to conform with DB2 syntax.

    (2) I couldn't see no effort to look for built-in functions and/or expressions in DB2.
    Because, I thought that the fuctionality of TestPmcDate might be realized by TIMESTAMP_FORMAT built-in function.

    (3) (S)he might not have programming skills(regardless of programming languages including SQL)
    Because, there were some incompleted/cofused codes in the function body of TestPmcDate which might be not seen in codes of skilled programmers.

Posting Permissions

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