Non-Padded Timestamp Format

eli.nawas_AUS · Post by **eli.nawas_AUS** » Mon Jun 02, 2014 10:42 am

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?

chulett · Post by **chulett** » Mon Jun 02, 2014 10:54 am

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

eli.nawas_AUS · Post by **eli.nawas_AUS** » Mon Jun 02, 2014 12:57 pm

As timestamp.

chulett · Post by **chulett** » Mon Jun 02, 2014 1:00 pm

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.

eli.nawas_AUS · Post by **eli.nawas_AUS** » Mon Jun 02, 2014 5:02 pm

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.

chulett · Post by **chulett** » Mon Jun 02, 2014 5:23 pm

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?

Mike · Post by **Mike** » Mon Jun 02, 2014 7:13 pm

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

eli.nawas_AUS · Post by **eli.nawas_AUS** » Thu Jun 05, 2014 2:28 pm

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"

chulett · Post by **chulett** » Thu Jun 05, 2014 3:08 pm

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?

ssnegi · Post by **ssnegi** » Thu Jun 05, 2014 3:27 pm

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

chulett · Post by **chulett** » Thu Jun 05, 2014 3:47 pm

Or we could just work on finding a proper solution.

Mike · Post by **Mike** » Thu Jun 05, 2014 7:56 pm

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

Mike · Post by **Mike** » Thu Jun 05, 2014 8:03 pm

Oh...and maybe

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

Mike

chulett · Post by **chulett** » Thu Jun 05, 2014 8:18 pm

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