Solved

Insert a datetime field into an oracle database ...

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sumDigits  challenge 7 74
changeXy challenge 13 67
firstChar challenge 13 106
Windows and other UI names - The Proper names for the Various elements! 1 55
A short article about a problem I had getting the GPS LocationListener working.
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…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

947 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

17 Experts available now in Live!

Get 1:1 Help Now