asked on

Insert a datetime field into an oracle database ...

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...

Thandava Vallepalli

Hello,

Have a look at these links,

http://groups.yahoo.com/group/AspNetOracle/message/19
http://groups.yahoo.com/group/AspNetOracle/messages/1?viscount=100
http://www.dbforums.com/t965734.html

Thandava Vallepalli

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

https://www.experts-exchange.com/questions/20641235/Inserting-date-to-oracle-db-with-php-URGENT-HELP-ME.html
http://beta.experts-exchange.com/questions/20681496/Simple-SQL-question.html

All the best,
V.Thandava Krishna.

Thandava Vallepalli

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.

ASKER CERTIFIED SOLUTION

Thandava Vallepalli

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D_M_D

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