timestamp format

devsonali · Post by **devsonali** » Fri Jul 01, 2016 10:52 am

Hello everyone

I wanted to know if there are any direct ways to change the timestamp format
(Tried doing in job properties) but it didn't work

file-transform-file

StringToTimestamp(TimestampColumn,"%yyyy-%mm-%dd %hh:%nn:%ss") is the standard format for any string to timestamp conversion But the above conversion expects the string in the same format and outputs the timestamp in same format

Lets say I want to convert from default timestamp format to mm/dd/yy hh:nn:ss format, Do I have to first convert the timestamp into string and then do a string to timestamp

If I were to do type conversion to achieve this where do I give the "expected" format ?

Thank you for looking

chulett · Post by **chulett** » Fri Jul 01, 2016 11:28 am

There is no "expected format"... a timestamp is a timestamp and only has an external format when you put it into a string. That's why the StringToTimestamp function wants to know what the string looks like but doesn't ask you what you want the timestamp to look like. Does that make sense? If you need a string in a different format then yes, you would first convert the original string to a timestamp and then convert the timestamp back out to a string in the different format.

devsonali · Post by **devsonali** » Fri Jul 01, 2016 11:31 am

Yes , It makes perfect sense . But if the output is a Timestamp Field and you want the timestamp in that specific format (different from the default datastge format) , How can you convert that (or is there a Tag to be added to make that conversion ) ?

chulett · Post by **chulett** » Fri Jul 01, 2016 1:19 pm

Well... if one of your fields in the target file needs a timestamp in a "different" format, AFAIK you'll need to define it as a string and build it out in that format. You may have a way to override the format as an advanced property of the field itself, others would have to confirm / deny that for us.

qt_ky · Post by **qt_ky** » Tue Jul 05, 2016 8:03 am

The only way I know of is to change your output column data type to Char or VarChar and then use a type conversion function on your Timestamp column. Then you can specify any of the valid tags with your formatting choices.

TimestampToString(lnk.TS, '%yyyy/%mm/%dd %hh:%nn:%ss')

If you have a subsequent Parallel job that reads this column from the file, then it will not recognize it as a timestamp, but rather as a string--Char or VarChar.

qt_ky · Post by **qt_ky** » Tue Jul 05, 2016 8:10 am

You could also experiment with overriding the default formats.

At the job level: in Designer, Job Properties, Defaults tab, uncheck Project default, change the value.

At the project level: in Administrator, select Project, Properties, Parallel tab, uncheck System default, change the value.

Job level is safer as it only affects one job at a time. Use an annotation to highlight the change though, otherwise people may easily clone the job in the future and get unexpected behaviors. Project level affects all jobs in the project.

devsonali · Post by **devsonali** » Tue Jul 05, 2016 8:47 am

Thank you all

"At the job level: in Designer, Job Properties, Defaults tab, uncheck Project default, change the value."

This is the first place I tested , but this test failed to change the format and I do not know why it is doing that ?

Secondly in the following conversion - Isn't the format we mention after the actual column refers to incoming data (in this case timestamp) format ?

TimestampToString(lnk.TS, '%yyyy/%mm/%dd %hh:%nn:%ss')

chulett · Post by **chulett** » Tue Jul 05, 2016 9:19 am

devsonali wrote:Secondly in the following conversion - Isn't the format we mention after the actual column refers to incoming data (in this case timestamp) format ?

TimestampToString(lnk.TS, '%yyyy/%mm/%dd %hh:%nn:%ss')

No, it always refers to the format of the string.

devsonali · Post by **devsonali** » Tue Jul 05, 2016 9:27 am

Thank you both .

I am able to convert it to non default format but as a string and not as a timestamp .
I will keep trying to troubleshoot/research on why I fail to change the format when I see change the default timestamp format in job properties .

qt_ky · Post by **qt_ky** » Tue Jul 05, 2016 2:21 pm

I also tested overriding the default via job properties on v11.3 with no difference. I guess that feature doesn't actually work. Good thing I've never had a need to use it!

qt_ky · Post by **qt_ky** » Tue Jul 05, 2016 2:40 pm

I tried it again... The peek stage does not honor the format override at the job level, but the sequential file stage does.

Certain stages also support overriding default data type formats at the stage level:
http://www.ibm.com/support/knowledgecen ... Level.html

devsonali · Post by **devsonali** » Wed Jul 06, 2016 7:02 am

That is good to know , I think this would have worked for me if the target was a sequential stage , However my target here is XML hierarchical stage.

I may have more than one issue here . When I try to map a field which is defined type as <xs:simpleType name="NonRequiredStringDateTime"> to a string or a timestamp field I get the following message when I try to select from input field

CDIUI2820E The mapping is not applicable due to an invalid type conversion or difference in the source and target list dimensions.( I am trying to research what does it me an by difference in source and target list dimentions)

Within the XSD
This type is defined as one of the following patters , However even if I convert the timestamp in one of these patterns , The above message is thrown while selecting an input column . The only option is to select macros for date and timestamp and when I give those the job fails.

<xs:pattern value="[0-1]?[0-9][/][0-3]?[0-9][/]([0-9][0-9])?[0-9]{2} [0-2]?[0-9]:[0-5][0-9] [a-zA-Z][a-zA-Z][a-zA-Z]" />
- 
<xs:pattern value="[0-3]?[0-9][/][0-1]?[0-9][/]([0-9][0-9])?[0-9]{2} [0-2]?[0-9]:[0-5][0-9] [a-zA-Z][a-zA-Z][a-zA-Z]" />
- 
<xs:pattern value="[0-3]?[0-9][.][0-1]?[0-9][.]([0-9][0-9])?[0-9]{2} [0-2]?[0-9]:[0-5][0-9] [a-zA-Z][a-zA-Z][a-zA-Z]" />
- 
<xs:pattern value="([0-9][0-9])?[0-9]{2}[\-][0-1]?[0-9][\-][0-3]?[0-9] [0-2]?[0-9]:[0-5][0-9] [a-zA-Z][a-zA-Z][a-zA-Z]" />
- 
<xs:pattern value="[0-1]?[0-9][/][0-3]?[0-9][/]([0-9][0-9])?[0-9]{2} [0-2]?[0-9]:[0-5][0-9]" />
- 
<xs:pattern value="[0-3]?[0-9][/][0-1]?[0-9][/]([0-9][0-9])?[0-9]{2} [0-2]?[0-9]:[0-5][0-9]" />
- 
<xs:pattern value="[0-3]?[0-9][.][0-1]?[0-9][.]([0-9][0-9])?[0-9]{2} [0-2]?[0-9]:[0-5][0-9]" />
- 
<xs:pattern value="([0-9][0-9])?[0-9]{2}[\-][0-1]?[0-9][\-][0-3]?[0-9] [0-2]?[0-9]:[0-5][0-9]" />
- 
<xs:pattern value="" />
-