Convert varchar to timestamp

sam334 · Post by **sam334** » Wed Apr 09, 2014 10:02 am

Have one more question again,

Have a job where input is SQL db and output is DB2 db. There appointment date is Varchar,12 in SQL db with the format of 201403021500 which I am trying to convert as 03/02/2014 3.00 PM (Timestamp)

Any help.
Thanks.

chulett · Post by **chulett** » Wed Apr 09, 2014 10:08 am

The "timestamp" part is the important part, not the specific format you posted. Have you tried using the StringToTimestamp function? What issues are you facing?

sam334 · Post by **sam334** » Wed Apr 09, 2014 10:12 am

Craig,
I used StringToTimeStamp(Input,"%mm/%dd/%yyyy %hh:%nn:%ss")

Is this correct? In transformer, it is not accepting it. Neither in Stagevariable.

chulett · Post by **chulett** » Wed Apr 09, 2014 10:25 am

Classic problem - the format mask you use in the function needs to match the format of the incoming string, not any perceived output format. Timestamps don't have an internal format so it just needs to know how to parse the string to turn it into a timetamp.

chulett · Post by **chulett** » Wed Apr 09, 2014 10:28 am

OK, hold on... is this a Server job as posted? If so, my bad, lost track of what forum we were in - that function is a Parallel function, which would be why it would not be "accepted". You'd either need to work with IConv/OConv or just substring it apart and put it back together in the format that you need.

sam334 · Post by **sam334** » Wed Apr 09, 2014 11:06 am

Thanks Craig. Unfortunate I can't see the explanation as my premium membership is still not activated. Its been almost a month gone, have sent lots of emails to admin. Not yet done..

chulett · Post by **chulett** » Wed Apr 09, 2014 1:05 pm

Opened them.

sam334 · Post by **sam334** » Wed Apr 09, 2014 2:31 pm

Craig,
Thanks a lot. I thought so. We have all the way top to bottom OCON/INCONV floating... lol.

Now, I used the below substrings function, which transformer is not accepting. Anything wrong with the "" in transformer.

As input is 201302031500, and output is 03/02/2013 3.00Pm.I used,

SUBSTRINGS(input,1,4):''-'':SUBSTRINGS(input,5,2):''-'':SUBSTRING(input,7,2):'' '':SUBSTRINGS(input,9,2):'':'':SUBSTRINGS(input,11,2):'':00''

Any thought.

sam334 · Post by **sam334** » Wed Apr 09, 2014 2:53 pm

Never mind.. Got it solved... Thanks a lot.. Appreciate as always...

ray.wurlod · Post by **ray.wurlod** » Wed Apr 09, 2014 2:54 pm

Except that the middle one needs to be SUBSTRINGS (I assume you made a typo here) that will work.

Another possbility - which I believe is better self-documented - is

Code: Select all

Fmt(InLink.TheValue, "L####-##-## ##:##:00")

chulett · Post by **chulett** » Wed Apr 09, 2014 2:56 pm

I prefer the operator over the function, but six of one...

input[1,4]:''-'':input[5,2]:''-'':input[7,2]: <etc>

sam334 · Post by **sam334** » Thu Apr 10, 2014 9:20 am

Thanks Craig and Ray. It worked perfectly.