Re: Loading dates with nullif clause

From: <>
Date: Sun, 30 May 1999 05:55:36 GMT
Message-ID: <7iqjso$83m$>

In article <>,   Kevan Forest <> wrote:
> I am trying to load a file that has dates in the format of MMDDYY.
> of the date fields have '000000' in them rather than a valid date. In
> this case I want to load nulls to the field.
> The problem I am having is that when I give SQL*LOADER the date format
> first, and the null if second, the load blows up on my zero records
> because the date is invalid. If I list the null if first, Oracle will
> not recognize my format statement which follows and assumes its
> date format.
> examples taken from control file:
> YR_PACKAGE_DATE position(238:243) date "MMDDYY" nullif
> (238:243)="000000")
> The above runs, but tries to validate the data using the MMDDYY format
> before evaluating my nullif statement, thus causing the records to be
> thrown out.
> YR_PACKAGE_DATE position(238:243) nullif (238:243)
> date "MMDDYY" )
> This bombs because oracle does not want the date "MMDDYY" in this
> location.
> If I remove the date "MMDDYY" statement all together Oracle throws out
> the non zero dates because they do not match the default Oracle
> Does anyone have any ideas other than changing the format of my
> file I am trying to load from?
> Kevan Forest
> Database Administrator
> University of Northern Iowa
Try this line instead.

YR_PACKAGE_DATE position(238:243) date "MMDDYY" nullif (YR_PACKAGE_DATE = "000000")

The example in the book doesn't show the nullif containing position values but the field name instead (I haven't tried this).

Sent via
Share what you know. Learn what you don't. Received on Sun May 30 1999 - 07:55:36 CEST

Original text of this message