Home » RDBMS Server » Server Utilities » Date format
Date format [message #368292] Wed, 01 November 2000 16:51 Go to next message
Neeraja
Messages: 8
Registered: November 2000
Junior Member
Good day to you,
I have written a SQL LOADER script which loads the data into oracle tables from a flat file. The problem is with the date field.
The date field is not null column.
The flat file contains both the values and also the nulls for that date fields. so, to handle that I wrote this script
DATE 'mm/dd/yyyy' NULLIF (BEGIN_DATE=BLANKS)

which will place blank values where ever the date is null. It works fine. But, now instead of Blanks I want to insert the SYSTEM DATE when there is a null for the date field. I tried using the NVL in this way

"NVL(rtrim(ltrim(date,'sysdate')));

but this command gives me system date for all the fields int he date column , even when there is a date.

Kindly help me.

Thanks a lot
Neeraja
Re: Date format [message #368297 is a reply to message #368292] Fri, 03 November 2000 18:43 Go to previous message
Thaniks
Messages: 8
Registered: October 2000
Junior Member
Hi Neeraja,

Please include the following line in yr script and this will work.

column_name "decode(:column_name,null,sysdate,:column_name)"

------------------------------------------------
Sample script:
------------------------------------------------
load data
infile '/export/home/oracle/temprt.dat'
badfile '/export/home/oracle/temprt.bad'
discardfile '/export/home/oracle/temprt.dsc'
discardmax 0
append
into table temprt
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(name ,
dob "decode(:dob,null,sysdate,:dob)" ,
sal
)

-------------------------------------------------
Thaniks.
Previous Topic: Export a role
Next Topic: Export/Import
Goto Forum:
  


Current Time: Sat May 21 04:26:53 CDT 2022