Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Unanswered: TIMESTAMP fomatting

    I'm having trouble formatting a date string from a TIMESTAMP column in 2 databases. One is DB2 version 10.1 and the other is 9.5.

    I want a string that returns the date in MMYYYYDD format regardless of the database version. (So, I can't use things like VARCHAR_FORMAT that don't work in my 9.5 version.)

    Also, the month and day values must be 2 digits and padded with zero for values less than 10.

    For NULL dates, I would like it to return the string "NULLDATE"

    Here's an example of what I want.


    TIMESTAMP_______________MMYYYYDD
    2013-10-08 12:02:30.101 10201308
    2013-09-02 00:12:09.443 09201302
    2013-01-10 00:01:03.555 01201310
    2009-07-22 04:04:45.987 07200922
    ________________________NULLDATE


    I found a solution that uses nested CASE statements, but it's UGLY. Is there an elegant solution that works in both versions?

    I can show you my solution and ask for something that works better, if you want. let me know.

    Thanks.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I thought VARCHAR_FORMAT function on DB2 9.5 for LUW might support format like 'MMYYYYDD',
    here is an example without using VARCHAR_FORMAT function.

    Example 1: Tested on DB2 Express-C 9.7.5 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT timestamp_value
         , COALESCE(
              TRANSLATE('efabcdgh' , HEX(timestamp_value) , 'abcdefgh')
            , 'NULLDATE'
           ) AS mmyyyydd
     FROM  (VALUES current timestamp
                 , '2013-10-08 12:02:30.101'
                 , '2013-09-02 00:12:09.443'
                 , '2013-01-10 00:01:03.555'
                 , '2009-07-22 04:04:45.987'
                 , NULL
           ) AS f(timestamp_value)
     ORDER BY
           COALESCE(CHAR(timestamp_value) , '0000') DESC
    ;
    ------------------------------------------------------------------------------
    
    TIMESTAMP_VALUE            MMYYYYDD
    -------------------------- --------
    2014-01-25-10.57.05.068000 01201425
    2013-10-08-12.02.30.101000 10201308
    2013-09-02-00.12.09.443000 09201302
    2013-01-10-00.01.03.555000 01201310
    2009-07-22-04.04.45.987000 07200922
    -                          NULLDATE
    
      6 record(s) selected.
    I tried many times to get various formatted string from date/time/timestamp data on older DB2 version.
    (That was fun.)
    But, it is troublesome for me to look for such past trials and works.
    Then I made new one.
    Last edited by tonkuma; 01-24-14 at 22:06.

Tags for this Thread

Posting Permissions

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