Numtodsinterval

edwin_fredrick · #1 (**permalink**) 06-01-11, 02:44

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:MI

S') AS SSTUDY_DATE_TIME
FROM TABLE1

tonkuma · #2 (**permalink**) 06-01-11, 04:14

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

edwin_fredrick · #3 (**permalink**) 06-01-11, 06:17

Sorry I forgot to mention i am using DB2 9.1 LUW.

And Yes, ISTUDY_DATE_TIME is a number column.

edwin_fredrick · #4 (**permalink**) 06-01-11, 07:39

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:MI

S') 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:MI

S') From TABLE1

tonkuma · #5 (**permalink**) 06-01-11, 08:12

Quote:

... 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

edwin_fredrick · #6 (**permalink**) 06-02-11, 01:16

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

tonkuma · #7 (**permalink**) 06-02-11, 03:24

Because, it was the only valid format string in DB2 V9.1 for LUW.

Note: TO_CHAR is a synonym for VARCHAR_FORMAT.

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech