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

    Question Unanswered: Oracle To DB2 todate(..) >= SUBSTR(date...)

    Hi,

    I want to convert the following Oracle query in to a DB2 query.

    select *
    FROM Vacation vac, BusinessEmployee emp
    where
    to_date(to_char(vac.startdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= SUBSTR(SYSDATE, 1, 10)

    The above query is to select all those rows that have the date value higher than the current date.

    This is part of a complex query and is executing well i Oracle.

    In DB2 i tried,

    SELECT
    TIMESTAMP(SUBSTR(CHAR(CHAR(DATE(vac.startdate), USA)), 7,4) || '-' || SUBSTR(CHAR(CHAR(DATE(vac.startdate), USA)), 1,2) || '-' || SUBSTR(CHAR(CHAR(DATE(vac.startdate), USA)), 4,2) || ' 00:00:00') >=
    SUBSTR(CURRENT TIMESTAMP, 1, 10)
    FROM Vacation vac, BusinessEmployee emp

    i got the error,
    [IBM][CLI Driver][DB2/LINUX] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007


    The left & right side of the comparison is working well when executed individually. The issue is in the comparison of them( a>= b)

    Also tell me whether Oracle is doing any implicit datatype conversion to compare the different datatypes here?.

    -gopidba
    Last edited by gopidba; 12-09-04 at 09:28.

  2. #2
    Join Date
    Oct 2004
    Posts
    4
    Since you're only interested about "did it happen before or after", why not do this:

    Code:
    select * 
    from Vacation vac, BusinessEmployee emp
    where timestampdiff(2, char(vac.startdate - current timestamp)) > 0
    Converting a date to character representation and then comparing it seems a bit wasteful... Also - are you sure your Oracle SQL is valid - you are selecting the date as 'Month-Day-Year' instead of a comparable 'Year-Month-Day' format. I would re-write the Oracle select as

    Code:
    select *
    FROM Vacation vac, BusinessEmployee emp
    where vac.startdate >= sysdate

  3. #3
    Join Date
    Nov 2004
    Posts
    26

    Question

    Hi okman,

    Thanks for the reply.

    I too understand the query given is not straight forward.

    But that is the existing query and we need to migrate it to DB2.

    My actual query is the one below.


    SELECT *
    FROM Vacation vac, BusinessEmployee emp
    WHERE
    vac.businessperson = emp.oid
    AND (to_date(to_char(vac.startdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= SUBSTR(SYSDATE, 1, 10)
    OR to_date(to_char(vac.enddate, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= SUBSTR(SYSDATE, 1, 10))
    ORDER BY emp.surname


    -gopidba

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work

    Code:
    SELECT * 
    FROM	Vacation vac, BusinessEmployee emp 
    WHERE	vac.businessperson  = emp.oid
     AND	(
    	vac.startdate  >= CURRENT DATE 
    	OR	
    	vac.enddate  >= CURRENT DATE
    	)
    ORDER BY emp.surname;
    But in DB2 you can also slow slow it down a lot by first converting the dates to strings

    Quote Originally Posted by gopidba

    SELECT *
    FROM Vacation vac, BusinessEmployee emp
    WHERE
    vac.businessperson = emp.oid
    AND (to_date(to_char(vac.startdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= SUBSTR(SYSDATE, 1, 10)
    OR to_date(to_char(vac.enddate, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= SUBSTR(SYSDATE, 1, 10))
    ORDER BY emp.surname

    Wim

Posting Permissions

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