time stamp conversion
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 32
- Joined: Fri Dec 02, 2005 10:44 pm
- Location: Chicago,IL
Re: time stamp conversion
Check for the OCONV() function in the help topics.
Thanks,
Pradeep
(Ten Hugs And Nine KisseS - THANKS)......is that true?
In GOD we trust,for all others we take cash.
Pradeep
(Ten Hugs And Nine KisseS - THANKS)......is that true?
In GOD we trust,for all others we take cash.
Re: time stamp conversion
Parallel jobs, no oconv. Well, you have to actually check this, the timestamp format within datastage IS in 24 hr format, so you should not have to do anything special.pradeepleon wrote:Check for the OCONV() function in the help topics.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is no Oconv() function in the parallel Transformer stage.
Convert the timestamp to a string, and substring off the am/pm designator (Right() function is ideal).
Substring out the hours field, convert to int8 and, if the designator is PM add 12. Convert back to string.
Use concatenation to reassemble the parts, then convert back to timestamp.
Use stage variables for intermediate results - it will make future maintenance much easier.
Convert the timestamp to a string, and substring off the am/pm designator (Right() function is ideal).
Substring out the hours field, convert to int8 and, if the designator is PM add 12. Convert back to string.
Use concatenation to reassemble the parts, then convert back to timestamp.
Use stage variables for intermediate results - it will make future maintenance much easier.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 32
- Joined: Fri Dec 02, 2005 10:44 pm
- Location: Chicago,IL
Re: time stamp conversion
Oh I'm sorry, I thought it to be for a server job.sud wrote:Parallel jobs, no oconv. Well, you have to actually check this, the timestamp format within datastage IS in 24 hr format, so you should not have to do anything special.pradeepleon wrote:Check for the OCONV() function in the help topics.
Thanks,
Pradeep
(Ten Hugs And Nine KisseS - THANKS)......is that true?
In GOD we trust,for all others we take cash.
Pradeep
(Ten Hugs And Nine KisseS - THANKS)......is that true?
In GOD we trust,for all others we take cash.
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
Use the following in Modify stage.
But in order for this to work, you will have to pre process your date to have two digits for day.
To get the scale of 3, in your target stage, set the default timestamp format to %yyyy-%mm-%dd %hh:%nn:%ss.6
Code: Select all
date_fmt:timestamp = timestamp_from_string [%mmm %dd %yyyy %hh:%nn:%ss] (date_tst)
To get the scale of 3, in your target stage, set the default timestamp format to %yyyy-%mm-%dd %hh:%nn:%ss.6
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
i am new to modify stage , i gave 5 specification with that code and changed the format to %yyyy-%mm-%dd %hh:%nn:%ss.6 , i am getting this error
Un-handled conversion error on field "UMIT_STS_DTM " from source type "string[26]" to destination type "timestamp":
source value="Jul 9 1998 12:01:51:346PM"; the result is non-nullable and there is no handle_null to specify a default value
how do i pre process that date to have two digits for day?
Un-handled conversion error on field "UMIT_STS_DTM " from source type "string[26]" to destination type "timestamp":
source value="Jul 9 1998 12:01:51:346PM"; the result is non-nullable and there is no handle_null to specify a default value
how do i pre process that date to have two digits for day?
hi sam here
If you are comfortable with the transformer then you can preprocess the date in a stage variable to get two digit day. Something like the following:
Specify a stage variable, say FixDay. Its derivation will be
Now in the derivation use
Now your target is not nullable. Hence you need to wrap the last derivation into NullToValue() and provide an appropriate value if null is encountered.
Specify a stage variable, say FixDay. Its derivation will be
Code: Select all
If Len(Field(in.Col, " ", 2)) = 1 then Field(in.Col, " ", 1):' 0':Field(in.Col, " ", 2,3) else in.Col
Code: Select all
StringToTimestamp(FixDay, "%mmm %dd %yyyy %hh:%nn:%ss")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
when i am trying to run by using the above code its giving me this warning
Transformer_6,0: Conversion error calling conversion routine timestamp_from_string data may have been lost
and data is coming as all *s , my date is like this
"Jul 6 1998 12:00:00:000AM" after Jul there are 2 spaces.
Transformer_6,0: Conversion error calling conversion routine timestamp_from_string data may have been lost
and data is coming as all *s , my date is like this
"Jul 6 1998 12:00:00:000AM" after Jul there are 2 spaces.
hi sam here
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Ok, then change the FixDay stage variable's derivation to the following:
The StringToTimestamp() derivation will remain the same. Read the date as varchar.
Code: Select all
If in.Col[5,1] = " " then in.Col[1,3]:" 0":in.Col[6,21] else in.Col
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.