Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    26

    Unanswered: Date Conversion from ORACLE to DB2

    i am trying to convert the following query in Oracle to DB2

    SELECT oid, round(enddate - startdate + 1) NoOfDays from table1

    What should be equivalent for the part,
    "round(enddate - startdate + 1)"

    The result i get the above query in Oracle i get the result as,
    yy-mm-dd hh:mm:ss:sss

    I tried using ROUND(DAYOFYEAR(enddate) - DAYOFYEAR(startdate) + 1,0)
    The result in DB2 for the above query is in INTEGER.


    In short,

    i want an equivalent DB2 query for the Oracle query,
    SELECT oid, round(enddate - startdate + 1) NoOfDays from table1
    SELECT oid, round(startdate + 1) NoOfDays from table1

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Can you give a little more detail on what:
    round(enddate - startdate + 1)
    does?

    What are the data types?

    DB2 doesn't (natively) have anything with this format:
    yy-mm-dd hh:mm:ss:sss

    Closest is a timestamp:
    yy-mm-dd-hh.mm.ss.ssssss
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Nov 2004
    Posts
    26
    Sorry i didn't put my query clearly.

    I have a Table with 2 DATE columns, 'startdate' and 'enddate'.

    If the table has entries as follows,

    --------------------------------------------------------------------
    StartDate EndDate
    --------------------------------------------------------------------
    2004-01-01 08:08:08.123456789 2004-02-01 05:06:07.123456789

    --------------------------------------------------------------------

    My expected result for 'enddate - startdate' then add a day with that,
    is '32'

    this is done by the query in Oracle as follows,

    SELECT ROUND(enddate - startdate + 1) NoOfDays FROM table1

    i.e ,
    SELECT enddate - startdate + 1 NoOfDays FROM table1
    gives 31.875....
    round of this value gives 32.


    My requirement is a DB2 query which gets me the same '32' from the above table.

    Thanks,
    Gopi.

  4. #4
    Join Date
    Nov 2004
    Posts
    374

    oracle to db2

    in db2 we have datatypes : date - time or timestamp
    if date is being used : very simple
    convert date to days and do calculation
    C:\dprop>db2 select days(col1),days(col2) from orates

    1 2
    ----------- -----------
    731902 731901


    if timestamp : convert columns to date
    date(col1)...; and repeat same operation
    days(date(col1))
    Best Regards, Guy Przytula

  5. #5
    Join Date
    Nov 2004
    Posts
    26
    Thank you Przytula.

    It works for me.

    Gopi.

Posting Permissions

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