Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Angry Unanswered: datediff function equivalent in DB2

    Hi,
    I have the following function in MS SQL Server 2000 that I'm trying to convert to IBM DB2:

    set @elapsed = datediff(ms, @clock, getdate());


    Can anyone let me know the db2 equivalent of this?
    Thanks

  2. #2
    Join Date
    Sep 2003
    Posts
    5

    Smile oops

    Saw the posting rules after I posted the message!
    I'm Using DB2 V8.1
    on AIX

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since some of don't know SQL Server, can you explain in functional terms what you are trying to do?

  4. #4
    Join Date
    Sep 2003
    Posts
    5

    More detail

    I'm doing some benchmark test where
    i have a parameter (clock) which is set by the CURRENT TIMESTAMP command before a process/SQL statements are executed. At the end I have a function that calculates elapsed time by getting the difference between current timestamp and recorded clock value.

    set elapsed = datediff(ms, clock, getdate());

  5. #5
    Join Date
    Dec 2002
    Posts
    134

    Re: More detail

    Check

    http://www7b.software.ibm.com/dmdd/l.../0211yip3.html

    In short you need combination of 2 functions - days and mindight_seconds

    regards,
    dmitri

  6. #6
    Join Date
    Sep 2003
    Posts
    5

    Smile Re: More detail

    Hi,
    Thanks Dimitri. I used the following statement and it worked:

    SET d_elapsed = timestampdiff(1, char(timestamp(d_clock)- timestamp(CURRENT TIMESTAMP)));

    Thanks again

  7. #7
    Join Date
    Dec 2002
    Posts
    134

    Re: More detail

    Originally posted by viswanat
    Hi,
    Thanks Dimitri. I used the following statement and it worked:

    SET d_elapsed = timestampdiff(1, char(timestamp(d_clock)- timestamp(CURRENT TIMESTAMP)));

    Thanks again
    The code above will provide an approximation (ignores leap years, assumed 30 days per month), but most likely for your task it's acceptable.

    BTW, you do not need to use timestamp function on "current timestamp". The following should work (assumed d_clock is a timestamp datatype)

    SET d_elapsed = timestampdiff(1, char(d_clock- CURRENT TIMESTAMP));

Posting Permissions

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