Oracle To DB2 todate(..) >= SUBSTR(date...)

gopidba · #1 (**permalink**) 12-09-04, 04:38

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

okman · #2 (**permalink**) 12-09-04, 04:53

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

gopidba · #3 (**permalink**) 12-09-04, 06:13

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

Wim · #4 (**permalink**) 12-09-04, 07:19

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

Internet Services

Web Development

Digital Marketing

Consumer Tech