Formating of Date and Timestamps for Schema Files

[etlbets]

Is there any help on Formating of Date and Timestamps for Schema Files.
I need them to load into Teradata with the correct format.

Here would be an example of a schema file. The input file is pipe delimited, however when DUE_DATE is null on input file, we get ||, so the format of 10 bytes below fails, and the process drops the record.


record {final_delim=end
, delim='|' } (
SEGMENT: nullable string[max=50] ;
GVP: nullable string[max=50] ;
SPA_LINE_NUMBER: nullable int32 ;
ORDERTYPE: nullable string[max=1] ;
SPA_ACCOUNT_SOURCE: nullable string[max=50] ;
ACTIVITYMRVNRV: nullable string[max=15] ;
REPORTING_MRV: nullable decimal[18,2] ;
DUE_DATE: nullable date {print_field, date_format='%mm/%dd/%yyyy', null_field='12/31/9999'};
DUE_DATE_DRIVER: nullable string[max=100] ;
};

[chulett]

Welcome to your own topic after splitting this from the post you jumped on the end of. Please edit your post and add the particulars for your environment.

[zulfi123786]

DUE_DATE: nullable date {print_field, date_format='%mm/%dd/%yyyy', null_field='12/31/9999'};
};

As per the schema defined if DUE_DATE is NULL then you should be getting '12/31/9999' if that is not the case then you need to change your Null Field value appropriately.

Balnk values are not accepted as Null Field value for Date columns so you could import them as string and convert appropriately

[etlbets]

Thanks for feedback. We are wanting to load these values by dynamically creating the schema file. Basically, we want them to be date values in the database. I don't know of method to convert them unless I was to read the file as a fixed layout, and then convert them to dates for the load. Which means I need to use 2 fixed layouts.