Highlighted

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

New Here ,
Jul 08, 2019

Copy link to clipboard

Copied

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_DEBDT_FIN
16/09/200529/04/2009
30/04/200930/06/2010
01/07/201031/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?

Views

111

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

New Here ,
Jul 08, 2019

Copy link to clipboard

Copied

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_DEBDT_FIN
16/09/200529/04/2009
30/04/200930/06/2010
01/07/201031/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?

Views

112

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 08, 2019 0
LEGEND ,
Jul 08, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 08, 2019 0
BKBK LATEST
Adobe Community Professional ,
Jul 08, 2019

Copy link to clipboard

Copied

     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")

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 08, 2019 0