OconvIconv on a time field

[JDionne]

I have a time column which looks like 0814
i need it to look like 08:14

i have tried to use the following code:
oconv(iconv(DSLink3.TransTime, "THM[2,2]"), "T:HM[2,2]")

I dont get an error nor do I get the any data. It leaves the column null. I am importing it into a sequencial stage where the data type is set to varchar 30. Any Idea why this isnt working?
Jim

[chulett]

I don't think there is a "T" conversion code... don't you need to use "MT" to do Time Conversions?

[JDionne]

I don't think there is a "T" conversion code... don't you need to use "MT" to do Time Conversions?

I honestly was going on "IF Date is D than Time is T"

ill give it a go
thanx
Jim

PS it didnt give me an error with T eather

[JDionne]

I changed my code from oconv(iconv(DSLink3.TransTime, "THM[2,2]"), "T:HM[2,2]") to oconv(iconv(DSLink3.TransTime, "MTHM[2,2]"), "MT:HM[2,2]")
and still didnt get any return
Jim

[essaion]

I think you have to let at least a blank between MT and the format...
It should look like

Code: Select all

oconv(iconv(DSLink3.TransTime, "MT HM[2,2]"), "MT:HM[2,2]")

, shouldn't it ?

[JDionne]

I think you have to let at least a blank between MT and the format...
It should look like

Code: Select all

oconv(iconv(DSLink3.TransTime, "MT HM[2,2]"), "MT:HM[2,2]")

, shouldn't it ?

I gave that a go with the same results.
Jim

[chulett]

Have you checked the online help?

There doesn't seem to be a 'size' portion for time conversions like there is for dates:

Code: Select all

Conversion Expression          Internal Value
X = Iconv("02:46", "MT")       X = 9960

Conversion Expression          External Value
X = Oconv(10000, "MT")         X = "02:46" 

Trouble is, it seems to want a seperator and defaults to the colon. Perhaps you should just substring it in two and stick it back together again with a colon in the middle yourself?

[JDionne]

Have you checked the online help?

There doesn't seem to be a 'size' portion for time conversions like there is for dates:

Code: Select all

Conversion Expression          Internal Value
X = Iconv("02:46", "MT")       X = 9960

Conversion Expression          External Value
X = Oconv(10000, "MT")         X = "02:46" 

Trouble is, it seems to want a seperator and defaults to the colon. Perhaps you should just substring it in two and stick it back together again with a colon in the middle yourself?

Changing it to MT did half of what I need. The source data comes in with out a : seperateing the hour and min. after running the modified code I only got the Hour not the min from the data.
I have always had a problem with substring in DS. I cant seem to find the syntax in the Help file. Can you show me that please?
Jim

[chulett]

Look in the Index for "[ ] operator". At its simplest, it is pretty simple - start position and length - so:

Code: Select all

FIELD1 = "0227"
FIELD1[1,2] = "02"
FIELD1[3,2] = "27"

[JDionne]

Look in the Index for "[ ] operator". At its simplest, it is pretty simple - start position and length - so:

Code: Select all

FIELD1 = "0227"
FIELD1[1,2] = "02"
FIELD1[3,2] = "27"

Yup Yup Yup thats the answer

oconv(iconv(DSLink3.TransTime[1,2] : ":" : DSLink3.TransTime[3,2], "MT"), "MT")

Thanx guys
Jim

[ray.wurlod]

I have a time column which looks like 0814
i need it to look like 08:14

i have tried to use the following code:
oconv(iconv(DSLink3.TransTime, "THM[2,2]"), "T:HM[2,2]")

I dont get an error nor do I get the any data. It leaves the column null. I am importing it into a sequencial stage where the data type is set to varchar 30. Any Idea why this isnt working?
Jim

There is a "T" conversion, but it's not for time.
For time conversions you need "MT".

• "MT" may optionally be followed by:
  "H" - use 12 hour clock rather than 24 hour clock
  "S" - include seconds
  "c" - designated separator character
  formatting specifications in square brackets

There's good on-line help about conversion. You can drill down from the topic "BASIC Tasks".

So, to solve your particular problem, if you really want to use Iconv() and Oconv(), use something like:

Code: Select all

Oconv(Iconv(DSLink3.TransTime,"MT"),"MT:[2,2]")

A more efficient mechanism is to use substringing and concatenation.

Code: Select all

DSLink3.TransTime[1,2] : ":" : DSLink3.TransTime[3,2]

[chulett]

A more efficient mechanism is to use substringing and concatenation.

Code: Select all

DSLink3.TransTime[1,2] : ":" : DSLink3.TransTime[3,2]

That's what I meant, Jim, when I said to "substring it in two" yourself. You really aren't gaining anything by running it thru the "MT" conversion here unless you feel the time may be invalid and then you need to use STATUS to check the return code from the conversion. I think that may be another part you are missing when you say got "no error" - you won't, per se. You need to read the docs on STATUS and use it after your conversion to check for errors, they won't just pop up in there on their own.

If you do want to try that, use Ray's first code sample - or break it into two steps (in a routine) and check each step.

[JDionne]

Wow, am I off base. But it is working now and I have a head ache, so im gona address this later today. Thanx for the input.
Jim