Query output based on date - Oracle 8i

Question

I am trying to write a very simple query to output data based
on date ranges, but I keep encountering an error.

When I do this statement:

select * from my.table
where startdate > '2008-01-01'

I get this error:

[ODBC][Ora]ORA-01861: literal does not match format string

When I format the select statement this way:

select * from my.table
where startdate > #2008-01-01#

i get this error:

[ODBC][Ora]ORA-00932: inconsistent datatypes: expected DATE
got NUMBER

Currently using CF8, and what I believe is Oracle 8i. I have
tried formatting this data in many other ways, but can't get it to
work. I know that when I query the database via access, it has no
problem returning results with the SQL date formatted like
#01/01/2008#

paross1 · Accepted Answer

You can use the Oracle to_date() function to convert your string to a date/time object:

select * from my.table
where startdate > to_date('2008-01-01', 'YYYY-MM-DD')

Or, since you are using an ODBC connection to Oracle, you can try using the ColdFusion CreateODBCDate() function:

select * from my.table
where startdate > #CreateODBCDate("2008-01-01")#

Or, as already suggested, use cfqueryparam with the appropriate CFSQLType, such as CF_SQL_TIMESTAMP instead of CF_SQL_INTEGER like you are attempting to do.

You can't use a "string" date value against a date/time column in an Oracle query, as it won't perform an implicit type conversion.

Phil

lazyvalet · Answer

Haha, yeah I just noticed that instead of CF_SQL_NUMERIC Ishould have been using CF_SQL_DATEEverything is working now for me, thanks for the help, Ireally appreciate it!

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded