Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    4

    Question Unanswered: using date functions in DB2

    Hi,

    I am new to DB2 and i and not sure how to use date in DB2.

    I'll use my examples in Oracle which i am more familar with and hope it can be shown to me how this can be done in DB2

    e.g

    select * from table1 A
    where A.date1 >= to_date('01-10-2009 4:01:00 PM ' , 'MM-DD-YYYY HH:MIS AM')
    and A.date1 <= sysdate

    i know in DB2 , there's a CURRENT TIMESTAMP but how do i change the to_date(xx) part ?

    appreciate your advise

    tks & rgds

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It looks like your date1 column is really a timestamp. So it would be something like:

    select * from table1 A
    where A.date1 >= '2009-10-01-16.01.00.000000'
    and A.date1 <= current_timestamp;
    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
    May 2009
    Posts
    4
    tks for your reply,

    i'll try it out

    rgds

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    select * from table1 A
    where A.date1 BETWEEN '2009-10-01-16.01.00.000000'
    and current_timestamp;

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The following scalar function comes closest to Oracle's "TO_DATE":
    Code:
    select * from table1 A
    where A.date1 >=
          TIMESTAMP_FORMAT('01-10-2009 04:01:00 PM', 'MM-DD-YYYY HH:MI:SS AM')
      and ...
    DB2 even provided a synonym for the TIMESTAMP_FORMAT() function: (surprise):
    TO_DATE()

    (Not sure if the "AM" format works; if not: remove it, change "HH" to "HH24" and replace "04" by "16".)
    This function is new since DB2 9 for z/OS; also DB2 for LUW has it (at least since v8).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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