Re: Approaches to combining date and time

Comments embedded
On 7 Jun 2003 13:02:02 -0700, ramon_at_conexus.net (Ramon F Herrera) wrote:

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

NOT TRUE. You use expressions in sqlloader to combine fields. Also you can load data in a staging table and have sql set up to do the calculation for you.

 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.

No comma needed
>
>(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.
>

Yes of course. If you want to do it this way you should add (12/24)+(45/(24*60))+(27/(24*60*60)) as you need to express the time as a fraction of a day.

>
>Thanks for your insight.
>
>-RAmon F. Herrera

Just read the sqlloader manual *carefully*

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Jun 07 2003 - 16:56:42 CDT