Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: date subtraction in DB2 ?

    Hi,

    In Oracle the date subtractions takes the time values also into account and gives the resultant as no.of days

    Eg : -

    startdate is 2004-07-12-04.00.00 (yyyy-mm-dd-hh.mi:ss)
    and enddate is 2004-07-12-06.00.00 (yyyy-mm-dd-hh.mi:ss)

    select (a.enddate-a.startdate)*24*60 from test a;

    In oracle the duration would be retrieved as 120

    In DB2, I used day function for the same as follows :

    select (DAY(a.enddate) - DAY(a.startdate)) * 24 * 60 from test a;

    It gave zero, since I have taken into account only the days.

    I need a single function which can give the no.of days by default considering the hours and converting it into no.of days as Oracle does.

    Please advice on the above and give some examples for the functios you suggest.

    Thanks,
    Sam

  2. #2
    Join Date
    Mar 2004
    Posts
    205
    I am sorry, in the above post the value 120 retrieved from Oracle is in minutes, how to get the same using DB2 functions

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In db2, date and time are stored in the datatype TIMESTAMP ...

    By default, in DB2, when you subtract two timestamp values, the output is in yyddmmhhss ... You can use the function TIMESTAMPDIFF to get the difference in terms of seconds, mins etc. Have a look at the SQL Reference

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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