Query output based on date - Oracle 8i

[lazyvalet]

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#

[JR__Bob__Dobbs-qSBHQ2]

You might try using cfqueryparam
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474

[JR__Bob__Dobbs-qSBHQ2]

This link might help.
http://www.adobe.com/devnet/server_archive/articles/cfqueryparam_oracle_databases.html

[lazyvalet]

Perhaps a step in the right direction.

I now have:

select * from my.table
where startdate > <CFQUERYPARAM VALUE="#01/01/2008#" CFSQLTYPE="CF_SQL_NUMERIC">

Now I get an error of:

[ODBC][Ora]ORA-01847: day of month must be between 1 and last day of month

Tried a few methods of formatting the month, but no dice.

[JR__Bob__Dobbs-qSBHQ2]

Try this.

select * from my.table
where startdate > <CFQUERYPARAM VALUE="01/01/2008" CFSQLTYPE="CF_SQL_TIMESTAMP">

or

select * from my.table
where startdate > <CFQUERYPARAM VALUE="2008-01-01" CFSQLTYPE="CF_SQL_TIMESTAMP">

[paross1 · Accepted solution]

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]

Haha, yeah I just noticed that instead of CF_SQL_NUMERIC I should have been using CF_SQL_DATE

Everything is working now for me, thanks for the help, I really appreciate it!