DSXchange

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

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.

anudeepdasari001 wrote:...help me out

Groucho Marx wrote:I'd love to help you out. Which way did you come in?

99999999(Said by businees to subract the value with 99999999 to get timestamp)-83885269(format we get in the source)=16:11:4730

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.

This is what the requirement in the mapping document

99999999 - DN_TIME_STAMP = Date (example: 99999999 - 83885269 = 16114730 (16:11:4730))

Source DN_Time_Stamp Datatype=varchar
target column datatype=timestamp


what is the way to do that,,Any suggestions welcome

Suggest you read my reply that you probably just missed.

hello chulett,

I am thinking those are hours,minutes and mill seconds,What you want me to know from business

Probably stating the obvious here, but 16114730 milliseconds does not equate to 16:11:4730 (as hh:mm:nnnn)

I would be going back to whomever provided you the spec and get clarification.

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.

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.