Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2012
    Posts
    7

    Unanswered: Using TO_DATE function

    Hello,

    I am trying to convert the following string literal to timestamp format.


    SELECT TO_DATE('Jan 1 2013 12:00AM', 'Mon DD YYYY HH:MIAM' )
    FROM DUAL;

    However, this is producing [IBM][CLI Driver][DB2/LINUXX8664] SQL20447N Format string "MON DD YYYY HH:MIAM" is not valid for the "TO_DATE" function. SQLSTATE=22007

    Any ideas how I can fix this?

    Thanks
    Zack

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't have a machine handy to test, but I'm pretty sure that you need a space between the MI and AM in the format string, whether or not that space is present in the actual string.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi zacksolutions,

    This works since DB2 9.7 for LUW.
    Which DB2 version do you use?

    Regards,
    Mark.

  4. #4
    Join Date
    Jul 2012
    Posts
    7

    Db2 9.5

    Hello,

    Thanks for your replies.

    Adding the space between the MI and AM did not help.

    The db2 version I am using is 9.5 on Linux.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by zacksolutions View Post
    The db2 version I am using is 9.5 on Linux.
    Please, check for the format string formats available here:
    TIMESTAMP_FORMAT scalar function
    Regards,
    Mark.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by zacksolutions View Post
    Hello,

    Thanks for your replies.

    Adding the space between the MI and AM did not help.

    The db2 version I am using is 9.5 on Linux.
    The format you are using is not supported by V9.5: IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  7. #7
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Your syntax is valid for v9.7, but is not valid for v9.5, because v9.5 timestamp_format does not accept textual month names(Month/Mon) whereas v9.7 does.

  8. #8
    Join Date
    Jul 2012
    Posts
    7

    db2 timestamp

    Ok, thanks so how can I manipulate the date to the format I want it in?

  9. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by zacksolutions View Post
    Ok, thanks so how can I manipulate the date to the format I want it in?
    The easiest way I think is to create a very simple java udf using java.text.SimpleDateFormat
    Regards,
    Mark.

Posting Permissions

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