Varchar to timestamp

[anudeepdasari001]

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

[ray.wurlod]

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.

[ray.wurlod]

...help me out

I'd love to help you out. Which way did you come in?

[anudeepdasari001]

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

[chulett]

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.

[anudeepdasari001]

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

[chulett]

Suggest you read my reply that you probably just missed.

[anudeepdasari001]

hello chulett,

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

[ShaneMuir]

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.

[chulett]

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.

[Benz]

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.