When a date string is passed to an Oracle function having an input type of DATE from an ODBC component, unexpected behaviors may occur when using DataDirect drivers. The correct behavior depends on converting the string to a date.

The following SQL returns an incorrect result from an ODBC component:
SELECT FUNC_DATE_TO_INT('01-MAR-2009') AS MYDATE FROM DUAL

Returns -6212998000

The same query in SQL Plus returns 1235865600.

Modifying the select statement to:
SELECT FUNC_DATE_TO_INT(TO_DATE('01-MAR-2009','DD-MON-YYYY')) AS MYDATE FROM DUAL

Returns the correct answer. Interestingly, the original SQL statement works as expected if Oracle's ODBC driver is used instead of the DataDirect driver.


The Oracle function:

CREATE OR REPLACE FUNCTION
FUNC_DATE_TO_INT (ora_date IN DATE) RETURN NUMBER IS

interval NUMBER;
v_hours NUMBER;
v_minutes NUMBER;
v_seconds NUMBER;
BEGIN
interval := ora_date - TO_DATE('01-01-1970 00:00:00','dd-mm-yyyy hh24:mi:ss');
v_hours := FLOOR(interval*24+0.008);
v_minutes := FLOOR(((interval*24)-v_hours)*60+0.008);
v_seconds := FLOOR((((interval*24-v_hours)*60)-v_minutes)*60) + v_minutes * 60 + v_hours * 3600; RETURN v_seconds;
END FUNC_DATE_TO_INT;