Varchar to timestamp
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
Varchar to timestamp
Source 99999999-83885269=16:11:4730 timestamp value(timestamp varchar type),, What is the way to convert it into target(datatype timestamp),,please provide valid answers and help me out
anudpETL
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard.
What do the two source numbers represent?
What (precisely) are you trying to achieve? (Hint: "16:11:4730" is not a timestamp.)
We could guess your requirement, but that would waste everyone's time were the guess incorrect.
What do the two source numbers represent?
What (precisely) are you trying to achieve? (Hint: "16:11:4730" is not a timestamp.)
We could guess your requirement, but that would waste everyone's time were the guess incorrect.
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: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Varchar to timestamp
anudeepdasari001 wrote:...help me out
Groucho Marx wrote:I'd love to help you out. Which way did you come in?
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: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
We got that.
99999999 - 83885269 =16114730
However, as Ray noted, that is not a timestamp. Time perhaps or a duration, but timestamp no. What do you need to do with it, where / how will it be stored? Guessing when you show it as "16:11:4730" that means minutes, seconds and milliseconds? Please confirm / deny and give us more in the way of details so we can provide some cogent help.
99999999 - 83885269 =16114730
However, as Ray noted, that is not a timestamp. Time perhaps or a duration, but timestamp no. What do you need to do with it, where / how will it be stored? Guessing when you show it as "16:11:4730" that means minutes, seconds and milliseconds? Please confirm / deny and give us more in the way of details so we can provide some cogent help.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
I just meant the bit after the last colon were the milliseconds... but yes, let's not say what we're thinking it is - get all of us some clarification.
And just want to re-emphasis the fact that the data you are showing us is not a timestamp, that would require a full date and time component. If you actually do need to build a timestamp from this you'll need to get the business requirements with the exact 'how' it should be built, date to use, etc.
And just want to re-emphasis the fact that the data you are showing us is not a timestamp, that would require a full date and time component. If you actually do need to build a timestamp from this you'll need to get the business requirements with the exact 'how' it should be built, date to use, etc.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I think you just want to subtract your column value from 99999999 and then insert ":" as you have shown in the above example.
Lets say that you have a value "83885269" DN_TIME_STAMP column. In transformer give a derivation something like this in a stage variable., say SV
99999999 - DN_TIME_STAMP => SV value would be 16114730
Then in the output column derivation give it as SV[1,2]:':':SV[3,4]:':':SV[5,8] which would result in 16:11:4730
Again it wont be a timestamp format. So, your target column cannot be timestamp. But still you can get the output by setting it as varchar and validate the same.
Lets say that you have a value "83885269" DN_TIME_STAMP column. In transformer give a derivation something like this in a stage variable., say SV
99999999 - DN_TIME_STAMP => SV value would be 16114730
Then in the output column derivation give it as SV[1,2]:':':SV[3,4]:':':SV[5,8] which would result in 16:11:4730
Again it wont be a timestamp format. So, your target column cannot be timestamp. But still you can get the output by setting it as varchar and validate the same.
Benjamin