Results 1 to 7 of 7

Thread: Numtodsinterval

  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: Numtodsinterval

    I am trying to convert the following Oracle Query to DB2, But not able to find the NUMTODSINTERVAL in DB2.

    SELECT SCASE_DESCRIPTION, TO_CHAR((TO_DATE('19700101','YYYYMMDD') + NUMTODSINTERVAL(ISTUDY_DATE_TIME + (5*60*60+30*60),'Second')), 'DD/MM/YY HH24:MIS') AS SSTUDY_DATE_TIME
    FROM TABLE1

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is data type of ISTUDY_DATE_TIME?

    I guessed that it was munber and contained duration(or interval time?) in seconds.

    The following query may be equivalent to yours.
    (DB2 can add or subtract time durations directly.
    See DB2 Basics: Fun with Dates and Times)
    Code:
    SELECT SCASE_DESCRIPTION
         , TO_CHAR(
              TO_DATE('19700101' , 'YYYYMMDD')
              + (ISTUDY_DATE_TIME + 5 * 60 * 60 + 30 * 60) Second
            , 'DD/MM/YY HH24:MI:SS'
           ) AS SSTUDY_DATE_TIME
     FROM  TABLE1
    This may be more natural in DB2.
    Code:
    SELECT SCASE_DESCRIPTION
         , TO_CHAR(
              TIMESTAMP('1970-01-01')
              + ISTUDY_DATE_TIME SECONDS + 5 HOURS + 30 MINUTES
            , 'DD/MM/YY HH24:MI:SS'
           ) AS SSTUDY_DATE_TIME
     FROM  TABLE1

  3. #3
    Join Date
    Oct 2004
    Posts
    60
    Sorry I forgot to mention i am using DB2 9.1 LUW.

    And Yes, ISTUDY_DATE_TIME is a number column.

  4. #4
    Join Date
    Oct 2004
    Posts
    60
    Thanks TONKUMA

    I still have one issue.

    When i execute the following query.. it executes fine..

    SELECT TO_CHAR(TIMESTAMP('1970-01-01 00:00:00' ) + (COALESCE(ISTUDY_DATE_TIME,0) + 5 * 60 * 60 + 30 * 60) Second, 'YYYY-MM-DD HH24:MIS') From TABLE1


    Whereas when i change the date format and execute, it is throwing me SQL0171N Error..

    SELECT TO_CHAR(TIMESTAMP('1970-01-01 00:00:00' ) + (COALESCE(ISTUDY_DATE_TIME,0) + 5 * 60 * 60 + 30 * 60) Second, 'DD/MM/YYYY HH24:MIS') From TABLE1

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... i am using DB2 9.1 LUW.
    So, please try
    Code:
    SELECT SCASE_DESCRIPTION
         , TRANSLATE(
              'gh/ef/cd ij:kl:mn' /* 'DD/MM/YY HH24:MI:SS' */
            , CHAR(
                 TIMESTAMP('1970-01-01')
                 + ISTUDY_DATE_TIME SECONDS + 5 HOURS + 30 MINUTES
              )
            , 'abcd_ef_gh_ij_kl_mn'
           ) AS SSTUDY_DATE_TIME
     FROM  TABLE1

  6. #6
    Join Date
    Oct 2004
    Posts
    60
    Thanks !! it works like a charm...

    But still i am wondering why when date format is changed it is throwing an exception in my earlier query. hmm

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Because, it was the only valid format string in DB2 V9.1 for LUW.

    Note: TO_CHAR is a synonym for VARCHAR_FORMAT.

    format-string

    A character constant that contains a template for how the result is to be formatted. The length of the format string must not be greater than 254 (SQLSTATE 42815). Leading and trailing blanks are removed from format-string, and the resulting substring must be a valid template for a timestamp value (SQLSTATE 42815). The content of format-string can be specified in mixed case.

    Valid format strings are:

    Code:
       'YYYY-MM-DD HH24:MI:SS'
    where YYYY represents a 4-digit year value; MM represents a 2-digit month value (01-12; January=01); DD represents a 2-digit day of the month value (01-31); HH24 represents a 2-digit hour of the day value (00-24; If the hour is 24, the minutes and seconds values are zero.); MI represents a 2-digit minute value (00-59); and SS represents a 2-digit seconds value (00-59).
    See Information Center for more detail:
    VARCHAR_FORMAT
    Last edited by tonkuma; 06-02-11 at 04:27.

Posting Permissions

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