Solved

Insert a datetime field into an oracle database ...

Posted on 2003-11-17
8
584 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Advice in Xamarin 21 107
collection output issue 9 58
learn programming 8 66
Problems moving Excel files from local drive to server 4 24
A short article about a problem I had getting the GPS LocationListener working.
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 fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

679 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