Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    3

    Red face Unanswered: Oracle=sysdate; DB2=?

    I want to convert this oracle query in DB2 :

    SELECT ...
    WHERE TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') > 20020101000000

    Could you help me plz?

    Thanks,
    Chipzzz.

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141

    Re: Oracle=sysdate; DB2=?

    You can define your own function.
    For example:
    CREATE FUNCTION myschema.to_date (T TIMESTAMP, MASK VARCHAR(26))
    RETURNS VARCHAR(26)
    LANGUAGE SQL
    CONTAINS SQL
    SPECIFIC to_date
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN
    CASE WHEN MASK = 'JHJJ-MM-TT-HH.MM.SS' THEN SUBSTR(CHAR(T),1,19)
    WHEN MASK = 'JHJJ-MM-TT' THEN SUBSTR(CHAR(T),1,10)
    ELSE CHAR(T)
    END;

    After that, you can use it

    select myschema.to_date(timestamp, 'JHJJ-MM-TT-HH.MM.SS') from ...

    You can also give a look at the "IBM DB2 Universal Database Porting Guide: Oracle to DB2 Version 7.2"
    at
    http://www7b.software.ibm.com/dmdd/l...05wilkins.html

    HTH
    Rodney Krick

  3. #3
    Join Date
    Oct 2002
    Posts
    3
    Thanks for this solution, but I has to do the query without function...

  4. #4
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by Chipzzz
    Thanks for this solution, but I has to do the query without function...
    you can try this

    values (current timestamp )

    marcos oliva

  5. #5
    Join Date
    Oct 2002
    Posts
    3
    Ok, it works with current timestamp, the replace and substr function
    Thanks

Posting Permissions

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