RE: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()

[Brad Howerter]

Title: RE: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()

Eric, since you're not specifying a sql type with cfqueryparam, your select statement will only work if your SQL engine can interpret the string passed to it.

 

Go to sqlplus and select sysdate from dual.  What format does it show by default?  I suspect it includes a timestamp.

 

Anyway, to get it to work, you have to choices.  You can use dateFormat to put your date into a string format that your Oracle database will understand, or you can use <cfqueryparam cfsqltype="CF_SQL_DATE"...

 

Here are examples.

 

Using dateFormat:

 

<cfquery name="rs" datasource="#request.dsn.select#">
 SELECT * FROM DUAL WHERE SYSDATE - 1 < '#DateFormat(createODBCDateTime(foo), 'dd-mmm-yyyy')#'
</cfquery>

 

<cfdump var=#rs#>

 

Note: This only works if your sql engine can convert the string in format dd-mmm-yyyy to a date.  Mine can.  To see if yours can, try this in sqlplus:

select * from dual where TRUNC(sysdate) = to_char(sysdate, 'dd-mmm-yyyy')

 

Using createODBCDate and createODBCDateTime:

 

<cfquery name="rs" datasource="#request.dsn.select#">
 SELECT * FROM DUAL WHERE SYSDATE - 1 < <cfqueryparam value=#createODBCDate(foo )# cfsqltype="CF_SQL_DATE">
</cfquery>

 

<cfdump var=#rs#>

 

<cfquery name="rs" datasource="#request.dsn.select#">
 SELECT * FROM DUAL WHERE SYSDATE - 1 < <cfqueryparam value=#createODBCDateTime(foo )# cfsqltype="CF_SQL_DATE">
</cfquery>

 

<cfdump var=#rs#>

 

-----Original Message-----
From: Davis, Eric [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 04, 2003 8:36 AM
To: '[EMAIL PROTECTED]'
Subject: RE: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()

#CreateODBCDateTime(url.dt)# evaluates to
    {ts '2003-09-03 18:40:32'}

--
Eric C. Davis
Programmer/Analyst I
Georgia Department of Transportation
Office of I.T. Applications
Web Applications Group
404.463.2860.199
[EMAIL PROTECTED]
-----Original Message-----
From: Brad Howerter [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 04, 2003 10:22 AM
To: '[EMAIL PROTECTED]'
Subject: RE: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()

What does #CreateODBCDateTime(url.dt)# evaluate to?
-----Original Message-----
From: Davis, Eric [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 04, 2003 7:35 AM
To: [EMAIL PROTECTED]
Subject: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()

When used together, CFMX 6.1, Oracle 8.05, CreateODBCDateTime() error out. The same code works fine in CFMX 6.0. Can anyone verify?

The code in use is:
---
<cfquery name="getOperator" datasource="mva">
        SELECT  oper_name, (etc)
        FROM    mva_assignment
        WHERE   x_datetime_insert = #CreateODBCDateTime(url.dt)#
                AND (etc)
</cfquery>
---
The exact error returned is:
---
Error Executing Database Query.
ORA-00904: invalid column name
---
The odd thing is that if the call is changed to CreateODBCDate(), it no longer errors out; the field is, however, a datetime field.

I'm not sure if this is a driver issue or a JDBC-ODBC Bridge issue, or some other issue entirely. I'm just looking for help for a colleague. Has anyone run into this?

--
Eric C. Davis
Programmer/Analyst I
Georgia Department of Transportation
Office of I.T. Applications
Web Applications Group
404.463.2860.199
[EMAIL PROTECTED]