Solved

Insert a datetime field into an oracle database ...

Posted on 2003-11-17
8
583 Views
Last Modified: 2010-04-17
Hello!

My name is Juan P. Valdez.  I am trying to insert a record from an access database to an oracle database that includes a datetime field.  The insert sentence is the following:

vfecha = Format(rs!Time, "dd-MM-yyyy hh:mm:ss")

        cn1.Execute ("insert into rh_ponchadas_empleados(cod_compania,secuencia,cod_empleado,fecha) values('" & rs2!no_cia & "' ," & rs!id & ", '" & vempleado & "','" & datevalue(vfecha) & "')")


The error that the database raise up is: ora-01843 "Not a valid month"

What ' wrong?

Thanks for your help...
0
Comment
Question by:jpvaldez
  • 4
8 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 9763337
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 9763352
Hello,


When inserting a date field, use the TO_DATE function.  Instead of simply
inserting '8/2/2002', insert TO_DATE('8/2/2002','MM/DD/YYY').

http://p2p.wrox.com/archive/oracle_asp/2002-08/1.asp
http://p2p.wrox.com/archive/oracle_asp/2002-08/2.asp

http://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/Q_20641235.html
http://beta.experts-exchange.com/Databases/Oracle/Q_20681496.html



All the best,
V.Thandava Krishna.
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 9763400
Hello,

Try like this,

vfecha = Format(rs!Time, "dd-MM-yyyy hh:mm:ss")

        cn1.Execute ("insert into rh_ponchadas_empleados(cod_compania,secuencia,cod_empleado,fecha) values('" & rs2!no_cia & "' ," & rs!id & ", '" & vempleado & "',TO_DATE('" & vfecha & "', 'dd-mm-yyyy' )")

All the best,
V.Thandava Krishna.
0
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 500 total points
ID: 9763523
hELLO,

To_Date Syntax:

http://www.techonthenet.com/oracle/functions/to_date.htm

To_date( ' & vfecha & "',  'DD-MM-YYYY HH:MI:SS' )

All the best,
V.Thandava Krishna.
0
 
LVL 10

Expert Comment

by:D_M_D
ID: 9763831
here are examples....

Insert
cn1.Execute ("insert into rh_ponchadas_empleados(cod_compania,secuencia,cod_empleado,fecha)
values('" & rs2!no_cia & "' ,'" & rs!id & "', '" & vempleado & "', (TO_DATE('" & vfecha & "', 'dd-mm-yyyy' ))")

Select
select to_char(fecha,'HH:MI:SS') from appointment

-------
D_M_D
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Device same like our heart 12 92
Recommendation vb6 to vb.net or others 14 174
egit plugin on eclipse 8 82
Vector, list Questions R 6 30
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question