datetime type

[Casiopea]

I am importing from a text file the records for a table and I have problems with the date field. The values are like '04-01-2009 10:28' and the field type on the table is date, although I have tried timestamp or interval day(0) to second(0) and it didn't work. It never works.
I am importing the information with access import wizard tool, where I have linked through odbc to oracle.
Greetings and thank you.

 

[ChrisHunt]

The oracle DATE type includes a time component as well - so you're using the right data type. Just how you get your data into it is probably more of an access question than an oracle one. Maybe one for forum181 ?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[LKBrwnDBA]

If you are using sql*loader or external files, you need to define the date format in the control file (or the access parameters):

LOAD DATA
-- etc --
INTO ...
(col1
,col2_dt   DATE "MM-DD-YY HH24:MI"
,.. etc ...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Casiopea]

Well, I got to put the datetime into the date field and it doesn't give an error.
From Visual basic I assigne:
rsoracle.fields("fecha_u")=Format(mydate, "DD-MM-YYYY HH:MM:SS")

mydate is "04-06-2009 10:15" (spanish format)

then, to check the value in the table, I make a select:
select to_char(fecha_u,'DD-MM-YYYY HH24:MI:SS') from pol_his;
it returns "04-06-2009 00:00:00".
Why it does not keep the time?

 

[Casiopea]

I have found out the problem. I had the format for the field en visual basic and a trigger in oracle like this:
BEGIN
IF :NEW.FECHA_U IS NULL THEN
:NEW.FECHA_U := NULL;
ELSE
:NEW.FECHA_U := to_dateNEW.FECHA_U,'DD/MM/YYYY HH24:MI');
END IF;
END;
if the trigger is active it doesn't store the time, however if I disable the trigger, it stores the format 'DD-MM-YYYY HH24:MI:SS' written in visual basic.
My question is why the trigger does not work.
Thank you

 

[ChrisHunt]

My question is why the trigger does not work.

My question is what is the trigger supposed to be doing? It seems pretty pointless.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Beilstwh]

Fecha_u is a date field. What the trigger is doing is an implicit conversion to the default format for a date in character and then converting it back to date. Your trigger is useless and should be removed.



Bill
Lead Application Developer
New York State, USA

 

[Casiopea]

Thank you, you are right.