Solved

Insert a datetime field into an oracle database ...

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
This is about my first experience with programming Arduino.
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now