Netezza Bulk Load and Null dates using SAS/ACCESS interface

[RonTrimnell]

We're trying to bulk load Netezza data using the SAS/ACCESS interface.  We've found one problem we can't resolve.  If the date in a data source is 12/31/9999, the value gets treated as null and loads into Netezza as a Null.  We want the actual date.  We added the BL_DATAFILE option to keep the datafile.  SAS is stripping out the date field.  Does anyone know if there is some way to get the date to pass through without getting changed?

[TomKari]

From a quick look at Netezza doc'n, it appears that 12/31/9999 is the maximum date value allowed in Netezza.

It looks like you're trying to do something legal. If it were me, I'd try a quick test of copying a small table from SAS to Netezza with date values 12/30/9999 and 12/31/9999, not using bulk load. They should both be loaded successfully. If they aren't, open a case with Tech Support.

If they both load successfully, maybe try loading the same small table, using bulk load?

These inter-product transfers can be tricky, but once they're resolved they usually work well.

Tom

[SASKiwi]

What is the data source and what is the column type of the date in the source?

It sounds like a data translation problem as SAS can store 31 Dec 9999 OK:

data _null_;

date = '31dec9999'd;

  format date date9.;

  put date = ;

run;

[gra_in_aus]

Try formatting the date to be datetime25.6 (from memory).  I have tried in the past creating tables in Netezza using SAS kept getting the same sort of issues. When I used the datetime format it resolved.

[LinusH]

You are sure that yo are trying to fill a DATE column in Netezza?

What format do have on that column in SAS?