Oracle's default date format is DD-MMM-YY if you do a simple select of a
date column from SQLPLUS. If you insert in that format it should work. I
would do some thing like this
INSERT INTO TABLENAME(DATECOLUMN) VALUE('15-Jan-01')
I have not connected through ODBC for Oracle I usually use Client. You
could also use the ToDate function in Oracle.
Thank You,
Peter
Peter J. MacDonald II
Creative Computing, Inc.
100 Middle Street
Lincoln, RI 02865
Phone: 401.727.0183 x208
Fax: 401.727.4998
Portable: 401.965.3661
E-MAIL: [EMAIL PROTECTED]
Web Page: www.creatcomp.com
-----Original Message-----
From: Ang�l Stewart [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 16, 2001 09:36
To: CF-Talk
Subject: Help:Oracle Date Issues and Error Message.
-------------------------------------------------------------
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00917: missing comma
SQL = "INSERT INTO
Empinfo_audit(EmplID,name,deptid,effdt,enddate,dayabs,reason) VALUES('1111',
'Bond,James', '1017', '{ts '2001-01-15 00:00:00'}', '{ts '2001-01-18
00:00:00'}', '3', 'A')"
------------------------------------------------------------------
Hello all,
I am trying to perform the simple insert statement above, going through ODBC
with CF4.5 Professional to an Oracle 8i database.
The CF Code is here:
-----------------------------------------------------------------
<CFQUERY NAME="UpdateRecord" DATASOURCE="xxx" username="xxxxxxx"
password="xxxxxxx">
INSERT INTO
Empinfo_audit(EmplID,name,deptid,effdt,enddate,dayabs,reason)
VALUES('#cur_emplid#',
'#Evaluate("name_#cur_emplid#")#',
'#Evaluate("deptid_#cur_emplid#")#',
<CFIF #Evaluate("effdt_#cur_emplid#")# neq
"">
'#CREATEODBCDATETIME(Evaluate("effdt_#cur_emplid#"))#',
</CFIF>
<CFIF #Evaluate("EndDate_#cur_emplid#")# neq
"">
'#CREATEODBCDATETIME(Evaluate("EndDate_#cur_emplid#"))#',
</CFIF>
'#Evaluate("dayabs_#cur_emplid#")#',
'#Evaluate("reason_#cur_emplid#")#')
</CFQUERY>---->
--------------------------------------------------------------------
I can't understand why I'm getting that error, as all these variables appear
to be correctly seperated by commas, and the syntax looks correct. This is
part of an input loop that is used to enter from multiple, dynamically
generated rows of data entered by the user in the previous form. I am
testing it now on just a single row of data.
Secondly:
My client INSISTS on entering dates in this format YYYYMMDD. I am unaware of
any date standard that uses that format.
Also, It seems that Oracle is rejecting that format as a valid date entry.
What..how..what the hell do I do to get this into Oracle properly using that
CreateODBCDATETIME function?????
Thanks in advance,
Gel.
www.carigamer.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
