timestamp format
Moderators: chulett, rschirm, roy
timestamp format
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
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
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.
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
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')
"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')
No, it always refers to the format of the string.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')
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
Certain stages also support overriding default data type formats at the stage level:
http://www.ibm.com/support/knowledgecen ... Level.html
Choose a job you love, and you will never have to work a day in your life. - Confucius
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]" />
- <!-- (M)M/(d)d/(yy)yy (h)h:mm TZN
-->
<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]" />
- <!-- (d)d/(M)M/(yy)yy (h)h:mm TZN
-->
<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]" />
- <!-- (d)d.(M)M.(yy)yy (h)h:mm TZN
-->
<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]" />
- <!-- (yy)yy-(M)M-(d)d (h)h:mm TZN
-->
<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]" />
- <!-- (M)M/(d)d/(yy)yy (h)h:mm
-->
<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]" />
- <!-- (d)d/(M)M/(yy)yy (h)h:mm
-->
<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]" />
- <!-- (d)d.(M)M.(yy)yy (h)h:mm
-->
<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]" />
- <!-- (yy)yy-(M)M-(d)d (h)h:mm
-->
<xs:pattern value="" />
- <!-- null
-->
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]" />
- <!-- (M)M/(d)d/(yy)yy (h)h:mm TZN
-->
<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]" />
- <!-- (d)d/(M)M/(yy)yy (h)h:mm TZN
-->
<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]" />
- <!-- (d)d.(M)M.(yy)yy (h)h:mm TZN
-->
<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]" />
- <!-- (yy)yy-(M)M-(d)d (h)h:mm TZN
-->
<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]" />
- <!-- (M)M/(d)d/(yy)yy (h)h:mm
-->
<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]" />
- <!-- (d)d/(M)M/(yy)yy (h)h:mm
-->
<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]" />
- <!-- (d)d.(M)M.(yy)yy (h)h:mm
-->
<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]" />
- <!-- (yy)yy-(M)M-(d)d (h)h:mm
-->
<xs:pattern value="" />
- <!-- null
-->