Empty Date field Export to Seq File
Moderators: chulett, rschirm, roy
Empty Date field Export to Seq File
I am converting some date fields from our default of 1900-01-01 to the empty string (''). I am writing them out to a sequential stage with a datatype of date nullable and a format of %dd/%mm/%yyyy. They are beng written to the file as **********. I used to do a SetNull on the fields but thought making them all empty would be better. Can I make DataStage stop defaulting empty dates to 10 asterisks or do I need to go back to the SetNull function? Where is this conversion taking place? Why does DS allow all asterisks in preference to an empty string? Ten asterisks is not a valid date.
Re: Empty Date field Export to Seq File
Hi Robinsontrobinson wrote:I am converting some date fields from our default of 1900-01-01 to the empty string (''). I am writing them out to a sequential stage with a datatype of date nullable and a format of %dd/%mm/%yyyy. They are beng written to the file as **********. I used to do a SetNull on the fields but thought making them all empty would be better. Can I make DataStage stop defaulting empty dates to 10 asterisks or do I need to go back to the SetNull function? Where is this conversion taking place? Why does DS allow all asterisks in preference to an empty string? Ten asterisks is not a valid date.
Probably the problem is in you changing the date to '' and writing it as a date field with the given format . Try writing it as varchar or char. I hope it will work.
Regards
Ashwin
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"" is not a valid value for a date data type. Therefore you can not write "" into a date. What you might be able to do is to represent NULL as "" (Null Field Value property) provided you make the output field nullable, and feed it nulls where you want "".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi, just want to give some ideas to you:
You may use empty string ('') to represent a null date when writing
sequential file ( by setting the "Null field value" property )
but when you want to read empty string ('') into a nullable date field,
datastage fatal error :
[ ... cannot have a "null_field" of length 0 on import ...]
so suggest you to use space(10) to represent null date instead of empty
string. Since you could re-use the generated sequential file by Datastage.
Hope it can help you.
You may use empty string ('') to represent a null date when writing
sequential file ( by setting the "Null field value" property )
but when you want to read empty string ('') into a nullable date field,
datastage fatal error :
[ ... cannot have a "null_field" of length 0 on import ...]
so suggest you to use space(10) to represent null date instead of empty
string. Since you could re-use the generated sequential file by Datastage.
Hope it can help you.
alphac
Yes, this is what I do. I set the NULL_VALUE to "" - the EMPTY String. It ONLY works if I NULL the date fields, otherwise the date fields will fill with asterisks. Using space(10) is almost as bad as asterisks. Luckily I use the APT_ALLOW_ZERO_LENGTH_NULL_ON_EXPORT_BLAH_BLAH_BLAH set to TRUE. This will get me away from having to use 10 spaces.
I'm not importing, strictly exporting.
I'm not importing, strictly exporting.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: