DSXchange

I have some timestamps that are formatted with nonpadded month, day, and hour fields ('%m/%d/%yyyy %h:%nn') in the output stage, but are being printed out with padded fields ('03/04/2013 00:00') in the file. Is this expected behavior?

How are they declared in the output Sequential File stage - as a string or as a timestamp?

As timestamp.

Which is why you are getting the default formatting in the file. If you really want control over it, declare the output field as a string and then use TimestampToString() to get the specific external format you are looking for. That or don't worry about it.

I am not getting the default, I am getting a non-default string that matches the string I expected except for the padding. The default string would be '%yyyy-%mm-%dd %hh:%nn:%ss'. The output format is specified in the output Sequential File stage, so it should output the value correctly.

OK, fine... "default" was a poor choice of word on my part as I wasn't referring to an ANSI standard timestamp. If you've actually specified that formatting for the field in the Sequential File stage and it's not respecting it then perhaps it is a bug. Unless someone has some advice on a different format string to try, I would suggest asking your official support provider.

What version are you on, by the way?

I seem to recall some ancient 8x documentation mentioning that those variable formats only work with the import operator (read). Check current documentation to see if that restriction still applies. You may have to go with Craig's suggestion if the export operator doesn't support the variable formats.

Mike

I am on version 9.1.0.0 according to the Help page.

The TimestampToString() function is not effective, I see the same problem:

TimestampToString(lnk_src.estimate_start_date,"%m/%d/%yyyy %h:%nn")

"03/09/2013 00:00"

Well, that sucks. According to the documentation what Mike thought turns out to still be true - those "variable width" time formats are only valid for the import operator.

"Where indicated the tags can represent variable-fields on import, export, or both. Variable-width date elements can omit leading zeroes without causing errors."

While some of the Date formats are valid for export, it would appear that none of the time ones are... which strikes me as odd. However, still seems like that shouldn't matter when using those formats in a conversion function like TimestampToString. Just to be anal, you did change your output field in the flat file to a string, yes?

you can remove the padding with an if condition and field function.
StageVariable :
svMnth : field(lnk_src.estimate_start_date,'/',1)
svMnthNP: if left(svMnth,1) = 0 then right(svMnth,1) else svMnth
svDay : field(lnk_src.estimate_start_date,'/',2)
svDayNP: if left(svDay,1) = 0 then right(svDay,1) else svDay
svYear : field(field(lnk_src.estimate_start_date,'/',3),' ',1)
svHour : field(field(lnk_src.estimate_start_date,' ',2),':',1)
svHourNP: if left(svHour,1) = 0 then right(svHour,1) else svHour
svNN : field(lnk_src.estimate_start_date,':',2)
svNNP: if left(svNN,1) = 0 then right(svNN,1) else svNN
svString :svMonthNP:'/':svDayNP:'/':svYear:' ':svHourNP:':':svNNP

Or we could just work on finding a proper solution.

Out of curiosity, I did a little experimenting. It does seem like the variable-width formats are limited to the import operator. Transform functions and the export operator seem to treat variable-width formats as if they were a fixed-width format.

That leaves me with these suggestions:
1) enhancement request to IBM
2) creative stage variable shenanigans
3) custom parallel routine
4) buildop
5) Oconv in a server shared container

Mike

Oh...and maybe

6) some awk/sed magic with the sequential file filter option

Mike

Well... it seems we already have the shenanigans.