C, unixODBC, oracle and Date queries

I've got a query like

select x from tableName where startDate <= now;

When i query the database without the date fields, everything works as expected. As soon as i start using date or timestamp columns in the oracle Database, my queries return nothing or an error.

What I do:

snprintf(sql, sizeof(sql), "SELECT roomNo, userPass, adminPass, adminFlags, userFlags, bookId, is_locked, running_on_server FROM booking WHERE roomNo = '?' AND startTime <=  { ts '?' }  AND endTime >= { ts '?' } for update;");                              
        stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps);

the ? will be replaced by values, with follo开发者_JS百科wing command:

SQLBindParameter(stmt, i + 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,  strlen(gps->argv[i]), 0, gps->argv[i], 0, NULL);

when I execute the query I get an error that TS is an invalid identifier [this was a recommendation by another board, taken from msdn - which may cause this error]

but even if I remove it and send just a string to the database, I'll get an empty result back. I also tried to bind the parameters as SQL_TIMESTAMP and SQL_DATE, but this didn't help either.

Hopefully somebody can help me.

thanks in advance. Chris

Are you sending a DATE data type to the Oracle query or a date represented in a string?

From the Oracle side of things, if you send a date in a string variable you'll need to use the oracle "TO_DATE" function (http://www.techonthenet.com/oracle/functions/to_date.php) to then convert thew string back to a date for use in your SQL statement (assuming startDate or startTime/endTime are DATE columns in the database).

Your fist SQL example should be:

SELECT x 
  FROM tableName 
 WHERE startDate <= TO_DATE(now, '<date-format>'); 

If the variable "now" was a string containing '05-OCT-2011 16:15:23' (including a time portion) then the to_date would be:

TO_DATE(now, 'DD-MON-YYYY HH24:MI:SS')

If you compare a string with a date and don't specify the format of that date Oracle will use its default NLS parameters and try to apply that format. Therefore it is always prudent to specify the date format using TO_DATE.

Hope it helps...