DATE and TIME fields problems 1

[NiteCrawlr]

Hi.... again

I need some help:

I have a txt file which have this information:
21/06/2001;11:01:36

and I have a table like:

CREATE TABLE LOGTABLE (
DAT_CIS DATE,
HOR_CIS DATE)

I'm having problems in making an INSERT in this table. I want the DAT_CIS to have only the date and the HOR_CIS to have only the hour. I've tried to use the SQL*loader but the data looks something like this:

DAT_CIS HOR_CIS
-------- --------
21/06/01 01/06/01

and I have a JAVA application which take the info from the table and use it. I've also tried to change the fild type from DATE to VARCHAR but the JAVA app stop functioning.

the insert comm I'm using is like:

insert into logtable (dat_cis, hor_cis) values ("12/12/01","13:04:00"

and the error is like:

ORA-00984: column not allowed

Thank you very much

 

[bcouse]

This is a common dilema. Most often, I would recommend storing date and time in separate columns as you have done. To further ease your burden, I would suggest storing the time in a varchar2(8) column in the format of HH:MI:SS if possible. If not, you just have to be aware of how te get data in and out of the column.

Also, whenever you attempt to insert data into that first date column, you may want to truncate the time portion of the date by using the TRUNC built-in (TRUNC(dat_cis)) to ensure that you will not have complications down the road with dates that also have a time component.

Enough of my ramblings...

SQL Loader should accept your data by telling it how the data is formatted ("DD/MM/YYYY" in the case of the date and "HH24:MI:SS" in the case of the time). If you are using a SQL Loader control file in your process, it might look something like this:

LOAD DATA
INFILE 'c:\temp\logfile.txt'
REPLACE
INTO TABLE logtable
FIELDS TERMINATED BY ;
(dat_cis date ("DD/MM/YYYY&quot, hor_cis date "HH24:MI:SS&quot

That should solve your SQL Loader problem.

As far as Oracle is concerned, there is a default date format on your system - "DD-MON-YY" is the standard default. This just means that if you want to see your date data in any other format than that ugly one, you need to tell Oracle how to display it. For instance:

select to_char(dat_cis,'DD/MM/YYYY'), to_char(hor_cis,'HH:24:MI:SS') should give you a result like:

to_char(dat_cis) to_char(hor_cis)
-------------------------------------
21/06/2001 11:01:36


Your problems with the insert command can be solved again by telling Oracle how your data is formatted. Remember, if the date data is not in the default format, you need to indicate to Oracle what that format is. For instance, your insert statement should like something like:

insert into logtable (dat_cis, hor_cis) values (to_date('12/12/2001','DD/MM/YYYY'),to_date('13:04:00','HH24:MI:SS'));

best-of-luck
bcouse...