Page 1 of 2
Project Default formatting
Posted: Thu Dec 03, 2009 2:54 am
by AmeyJoshi14
Hi,
We are facing problem in our project , we are using ISValid('Timestamp',InputColumn) to check whether the InputColumn is a valid timestamp or not.
The format of the input string is '20Nov2009 14:10:10',but in project default value is %dd-%mm-%yyyy %hh:%nn:%ss. I have unchecked the project default box and gave the desried value that is '%dd%mmm%yyyy %hh:%nn:%ss'
but it is not working...still it is considering the format as mentioned in the default project.
Also i have gone through one of the post and tried all teh options mentioned , but no luck
viewtopic.php?t=95920&highlight=project+default
My question is, can we override the project default value and use the format in which data is coming ...?
Thanks all for the help!!
Posted: Thu Dec 03, 2009 4:12 am
by balajisr
Convert to timestamp using function StringToTimestamp and then check for IsValid.
Posted: Thu Dec 03, 2009 2:28 pm
by ray.wurlod
Weak advice - the conversion function will fail if the string is not a valid timestamp. The IsValid() function is intended to be applied to a string.
Posted: Thu Dec 03, 2009 11:11 pm
by balajisr
Ray,
My project default timestamp format is "%yyyy-%mm-%dd %hh:%nn:%ss"
I have two input rows which has to be in format "%dd%mmm%yyyy %hh:%nn;%ss"
1.20Nov2009 14:10:10
2.Nov202009 14:10:10
In the transfomer i have the following code:
Code: Select all
IsValid('Timestamp',StringToTimestamp(DSLink2.Col1,'%dd%mmm%yyyy %hh:%nn;%ss'))
IsValid() Output:
1.20Nov2009 14:10:10 = 1
2.Nov202009 14:10:10 = 0
Anything wrong with this approach? Please advise.
Posted: Fri Dec 04, 2009 6:39 am
by ray.wurlod
Yes. The StringToTimestamp() function will fail before the IsValid() function is even executed, if the source column is not a valid timestamp based on the given format string. You need to test with IsValid() first. And in this case you probably need two tests, one for each format.
Posted: Sun Dec 06, 2009 10:48 pm
by AmeyJoshi14
balajisr wrote:Ray,
My project default timestamp format is "%yyyy-%mm-%dd %hh:%nn:%ss"
I have two input rows which has to be in format "%dd%mmm%yyyy %hh:%nn;%ss"
1.20Nov2009 14:10:10
2.Nov202009 14:10:10
In the transfomer i have the following code:
Code: Select all
IsValid('Timestamp',StringToTimestamp(DSLink2.Col1,'%dd%mmm%yyyy %hh:%nn;%ss'))
IsValid() Output:
1.20Nov2009 14:10:10 = 1
2.Nov202009 14:10:10 = 0
Anything wrong with this approach? Please advise.
Sorry for the late reply..
I actually use the above function, the job is giving warning..as Ray has pointed.. for every records ,whose date fomat doesn' t matches with the desired date format.
While searching in this forum , i found that inorder to achieve this, we have to use stage variables...
Is there any patch or any other method to achieve this..ideally if we uncheck the "Project Default" box and give the desired format ..it should work ...right??
Thanks all for the reply...
Posted: Sun Dec 06, 2009 11:09 pm
by balajisr
I actually use the above function, the job is giving warning..as Ray has pointed.. for every records ,whose date fomat doesn' t matches with the desired date format.
Can you please post those records which gives warning?
Posted: Sun Dec 06, 2009 11:37 pm
by AmeyJoshi14
balajisr wrote:I actually use the above function, the job is giving warning..as Ray has pointed.. for every records ,whose date fomat doesn' t matches with the desired date format.
Can you please post those records which gives warning?
Hi,
Thanks for the reply!!
Code: Select all
StringToTimestamp(Input.Column,'%dd%mmm%yyyy %hh:%nn:%ss')
For the below records its working fine:
'20Nov2009 14:10:10'
'21Nov2009 14:10:10'
'22Nov2009 14:10:10'
'23Nov2009 14:10:10'
But for the other records,it is giving me warning:
20112009 14:11:10
22112009 14:11:10
Posted: Mon Dec 07, 2009 12:09 am
by ray.wurlod
"11" does not match "%mmm" (which expects three characters). You must get your dates in to a consistent format.
Posted: Mon Dec 07, 2009 12:52 am
by AmeyJoshi14
ray.wurlod wrote:"11" does not match "%mmm" (which expects three characters). You must get your dates in to a consistent format. ...
Thanks for the reply!!
Actually I want to check whether the Timestamp from the input string IsValid.... So i have used the IsValid function, but the problem is , format , datastage consider only those date format as valid date which are mentioned in project default, even though I have uncheck the project default and gave the desired format.
Is there is any patch for the same.... by going through this forum i have found that I have to use stage variables to check the valid timestamp...
Its really strange....
even though i have uncheck the project default...still its considering the project default format as valid..
Again thanks for the all the help...
Posted: Mon Dec 07, 2009 1:06 am
by balajisr
Why don't you try using Ray's IsValid() routine. You need to develop server jobs to use this.
Posted: Mon Dec 07, 2009 1:28 am
by AmeyJoshi14
balajisr wrote:Why don't you try using Ray's IsValid() routine. You need to develop server jobs to use this.
Thanks of the reply!
Actually the problem is that we can't use the server job....(we have to develop only parallel jobs...
)..... I would like to use .....Ray's IsValid() routine.... :D
Posted: Mon Dec 07, 2009 1:40 am
by balajisr
Actually the problem is that we can't use the server job....(we have to develop only parallel jobs... Rolling Eyes )....
That's ridiculous.
FYI:
Code: Select all
IsValid('Timestamp',StringToTimestamp(DSLink2.Col1,'%dd%mmm%yyyy %hh:%nn;%ss'))
The above code with below data did not produce any warnings when i ran the job.
20112009 14:11:10
22112009 14:11:10
Is it probably due to version 7.5?
Posted: Mon Dec 07, 2009 3:03 am
by AmeyJoshi14
balajisr wrote:Actually the problem is that we can't use the server job....(we have to develop only parallel jobs... Rolling Eyes )....
That's ridiculous.
FYI:
Code: Select all
IsValid('Timestamp',StringToTimestamp(DSLink2.Col1,'%dd%mmm%yyyy %hh:%nn;%ss'))
The above code with below data did not produce any warnings when i ran the job.
20112009 14:11:10
22112009 14:11:10
Is it probably due to version 7.5?
Yes, I think for the 8X version , this problem exists...
I am really unable to unsderstand why the project default checkbox is not working...
Is it because there is some problem while installation...or is there any patch for the same...
Thanks for the reply !!
Posted: Mon Dec 07, 2009 3:48 am
by balajisr
Transformer date format is ISO 8601 standard (YYYY-MM-DD) regardless of project or job default date format.