Oracle request with date comparison and date formatting in .cfm script

[planetseb]

Hi,

I develop an application for management of meetings in CF with an Oracle DB. Each meeting is linked to a unit (UNIT_ID)

I'm trying to execute queries on fields and I obtain an Orable error:

So I have 2 tables with DATA fields (DD/MM/YYYY)

Table UNITS: UNIT_ID (PK) - UNIT_TITLE - DT_DEB - DT_FIN

Table MEETING: meeting_ID (PK) - TITLE - DATECREA - UNIT_ID

The request for getting the creation date of the meeting:

SELECT TO_DATE(DATECREA,'DD/MM/YYYY') AS DATECREA FROM MEETING M WHERE M.meeting_ID = 45533

The result: 30/05/2010

The request for getting the start and the end dates of all unit versions of the meeting unit

SELECT UNIT_TITLE, TO_DATE(DT_DEB,'DD/MM/YYYY') as DT_DEB, TO_DATE(DT_FIN,'DD/MM/YYYY') AS DT_FIN
FROM UNITS 
WHERE UNIT_ID = 231057

The results:

DT_DEB	DT_FIN
16/09/2005	29/04/2009
30/04/2009	30/06/2010
01/07/2010	31/12/2011

I need to execute this query now for getting the good version of the unit used for the meeting

<cfquery name="qry_existInAppref" datasource="#application.datasource#">
     SELECT UNIT_TITLE
     FROM UNITS
     WHERE ORG_ID = <cfqueryparam value="#arguments.org_id#" null="no" cfsqltype="cf_sql_numeric"/>
     AND TO_DATE(DT_DEB,'DD/MM/YYYY') > TO_DATE('#qry_reunion.DATECREA#','DD/MM/YYYY')
     AND TO_DATE(DT_FIN,'DD/MM/YYYY') < TO_DATE('#qry_reunion.DATECREA#','DD/MM/YYYY')
</cfquery>

I obtain the following error after executing the cfm script: ORA-01861: literal does not match format string\nORA-02063

SELECT * FROM UNITS_NOT_IN_APPREF 
WHERE ORG_ID = (param 1) 
AND TO_DATE(DT_DEB,'DD/MM/YYYY') < TO_DATE('2010-05-30 00:00:00.0','DD/MM/YYYY') 
AND TO_DATE(DT_FIN,'DD/MM/YYYY') > TO_DATE('2010-05-30 00:00:00.0','DD/MM/YYYY')

I cannot solve this error even if the dates are in the same format.

Could you please help me with that?

[WolfShade]

According to Oracle Docs, TO_DATE returns a date value.  Since it is worded specifically like that, I have to wonder if there is a difference between a date value and a date object.

If you remove TO_DATE() from the WHERE clause in your last query, does that work?  I mean:

AND DT_DEB < <cfqueryparam type="cf_sql_date" value="#yourDateValue#" />

HTH,

^ _ ^

[BKBK]

     AND TO_DATE(DT_DEB,'DD/MM/YYYY') > TO_DATE('#qry_reunion.DATECREA#','DD/MM/YYYY')

     AND TO_DATE(DT_FIN,'DD/MM/YYYY') < TO_DATE('#qry_reunion.DATECREA#','DD/MM/YYYY')

In those 2 lines, replace qry_reunion.DATECREA with dateformat(qry_reunion.DATECREA, "DD/MM/YYYY")