I use a DAO CFC including Oracle queries.
Now i recognized, that the to_char() and to_date() functions are no
longer necessary in order to select and update date fields?
Is this a new behaviour in Oracle 9, CFMX 6.1 or in the new JDBC
Drivers?
Example 1:
Select date field
<cfset var qtesttable = "">
<cfquery name="qtesttable" datasource="#dsn#">
SELECT
testdate,
to_char(testdate, 'MM/DD/YY HH24:MI:SS') AS testdate2,
to_char(testdate, 'MM/DD/YYYY HH24:MI:SS') AS testdate3,
to_char(testdate, 'YYYY/MM/DD HH24:MI:SS') AS testdate4
FROM testtable
</cfquery>
no format: #qtesttable.testdate#<br>
format 11: #DateFormat(qtesttable.testdate,"dd.mm.yyyy")#
#TimeFormat(qtesttable.testdate,"HH:mm:ss")#<br>
format 2: #DateFormat(qtesttable.testdate2,"dd.mm.yyyy")#
#TimeFormat(qtesttable.testdate2,"HH:mm:ss")#<br>
format 3: #DateFormat(qtesttable.testdate3,"dd.mm.yyyy")#
#TimeFormat(qtesttable.testdate3,"HH:mm:ss")#<br>
format 4: #DateFormat(qtesttable.testdate4,"dd.mm.yyyy")#
#TimeFormat(qtesttable.testdate4,"HH:mm:ss")#<br><br>
I always get correct results (under CFMX 6.1). I had to use to_char in
earlier applications.
Example 2:
Update date field
<cfset var thisdate =
CreateODBCDateTime(CreateDateTime(2004,3,2,12,32,30))>
<cfset var qUtesttable = "">
<cfquery name="qUtesttable" datasource="#dsn#">
UPDATE testtable
SET testdate = #CreateODBCDateTime(thisdate)#
</cfquery>
There are 4 ways to update a date field
1 #CreateODBCDateTime(thisdate)#
2 <cfqueryparam cfsqltype="CF_SQL_DATE"
value="#CreateODBCDateTime(thisdate)#"> - time information is lost
3 <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP"
value="#CreateODBCDateTime(thisdate)#">
4 to_date('#Dateformat(thisdate,'mm/dd/yyyy')#
#TimeFormat(thisdate,'HH:mm:ss')#', 'MM/DD/YYYY HH24:MI:SS')
Data is always updated in the correct format. I had to use to_date in
earier applications.
Changing the NLS_DATE_FORMAT parameter has no consequences.
<cfquery name="qAS" datasource="#dsn#">
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'
</cfquery>
Harry
--
Harry Klein | Konzeption und Entwicklung
CONTENS Software GmbH
Oettingenstr. 25 | 80538 M�nchen
Fon: +49 (0)89 5199 69-0 | Fax: +49 (0)89 5199 69-78
mailto:[EMAIL PROTECTED] | http://www.contens.de
******************************************
Besuchen Sie CONTENS auf der
iEX Internet Expo 2004 in Z�rich
04. - 06. Februar 2004
Halle 5, Stand 5.163
Mehr Infos finden Sie hier:
www.contens.de/iex <http://www.contens.de/iex>
******************************************
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
