Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: Not working in db2

    Hello,

    Consider this as a oracle Query
    [start]
    select fg.PROJECT_ID id
    from fgt_assignments fg
    where to_char(fg.UPDATED_ON,'mm-dd-yyyy') >= to_char(CURRENT_DATE - 1,'mm-dd-yyyy')
    -- and to_char(fg.UPDATED_ON,'mm-dd-yyyy') <= to_char(CURRENT_DATE,'mm-dd-yyyy')
    union
    select tp.PROJECT_ID id
    from tpt_expenses tp
    where to_char(tp.UPDATED_ON,'mm-dd-yyyy') >= to_char(CURRENT_DATE - 1,'mm-dd-yyyy')
    -- and to_char(tp.UPDATED_ON,'mm-dd-yyyy') <= to_char(CURRENT_DATE,'mm-dd-yyyy')
    [end]

    can anyone of you convert this into db2 Query, I did try it using cast() method into char but didn't work. Is there any other way to go about this.

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You don't need to cast the dates, just use regular date arithmatic. See the SQL Reference Manual, Vol 1 for datails on how date arithmatic works.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2007
    Posts
    56
    Thanks you mean to say that i should do as follows
    [Start]
    select assign.PROJECT_ID id from fgt_assignments assign
    where assign.UPDATED_ON >= current timestamp - 1
    and assign.UPDATED_ON <= current timestamp
    union
    select expenses.PROJECT_ID id from tpt_expenses expenses
    where expenses.UPDATED_ON >= current timestamp - 1
    and expenses.UPDATED_ON <= current timestamp
    [End]

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The only missing piece is the unit on the "-1". You have to tell the system whether you want to substract 1 year, 1 month, or 1 day.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by donraja_ht
    Thanks you mean to say that i should do as follows
    ...
    Not exactly. What I meant is that you should read the manual I mentioned (the manual also has examples to help you).

    You can download the PDF manuals for free.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2007
    Posts
    56
    i HAVE GOT IT right with some % here is the Query

    [start]
    select exp.PROJECT_ID id, day(exp.UPDATED_ON) from expenses as exp
    where date(exp.UPDATED_ON) >= current date
    [end]
    In the Table UPDATED_ON is of type Timestamp, but my problem is How to get one day back from the date as UPDATED_ON
    [start]
    select exp.PROJECT_ID id from expenses AS exp
    where DATE(exp.UPDATED_ON) <= DATE(current Date) - DAY(current Date) - 1
    [end]
    The Current date -1 does not work is there any other way to get the date one day back.

    and can you please post the links for the PDF Manuals from where i can download it

    Thanks.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Here is version 9.5 manuals
    IBM - DB2 Version 9.5 for Linux, UNIX and Windows English manuals

    Here is 9.1
    IBM - DB2 9 for Linux, UNIX and Windows manuals

    Here is 8.2
    IBM - DB2 UDB Version 8 Product Manuals

    The Current date -1 does not work is there any other way to get the date one day back.
    where exp.UPDATED_ON <= current date - 1 day
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Most of the things you are asking are quite basics.
    For new to DB2 UDB this is a good starting point. Free cookbook

    http://mysite.verizon.net/Graeme_Bir...k/DB2V95CK.PDF
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

Posting Permissions

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