Approaches to combining date and time

I am investigating several approaches the an interesting and very common problem. It is often the case that the date and the time of the day are separated when you want them to be an integrated entity. The *.dat file will contain something like this:

12/02/2003, 091247

or perhaps:

02-Dec-2003, 09:12:47

I am considering 3 alternative approaches to solve that problem.

(1) Combine the fields at SQL*LOADER time.

For this approach to work, the date and time fields have to be consecutive, and the control file should contain a line as follows:

LOAD DATA INFILE * APPEND INTO TABLE test
( d position (1:18) date external 'yyyymmdd, hh24:mi:ss',

Notice that the comma is inside and hence your table will end up with n-1 fields.

(2) Combine the fields with Text Concatenation

   c_FullDate CHAR(18);
   time CHAR(6);

   c_FullDate := TO_CHAR(myTable.Date) || substr(time, 1, 2) || ':' || ...

(3) Combine the fields with Integer Addition

   i_FullDate DATE;
   time NUMBER(6); // i.e. 124527 means 12:45:27

   i_FullDate := myTable.Date + time / 1000000;

That's what I have so far, at this point I'd like to hear more advanced comments to all this. BTW: the approach number (3) using addition is giving me some incorrect results.

Thanks for your insight.

-RAmon F. Herrera Received on Sat Jun 07 2003 - 15:02:02 CDT