Page 1 of 1
OconvIconv on a time field
Posted: Mon Feb 09, 2004 9:51 am
by 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
Posted: Mon Feb 09, 2004 9:56 am
by chulett
I don't think there is a "T" conversion code... don't you need to use "MT" to do Time Conversions?
Posted: Mon Feb 09, 2004 9:58 am
by JDionne
chulett wrote: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
Re: OconvIconv on a time field
Posted: Mon Feb 09, 2004 10:01 am
by 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
Posted: Mon Feb 09, 2004 10:08 am
by 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 ?
Posted: Mon Feb 09, 2004 10:53 am
by JDionne
essaion wrote: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
Posted: Mon Feb 09, 2004 11:24 am
by 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?
Posted: Mon Feb 09, 2004 11:52 am
by JDionne
chulett wrote: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
Posted: Mon Feb 09, 2004 12:25 pm
by 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"
Posted: Mon Feb 09, 2004 12:39 pm
by JDionne
chulett wrote: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
Re: OconvIconv on a time field
Posted: Mon Feb 09, 2004 7:49 pm
by ray.wurlod
JDionne wrote: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]
Re: OconvIconv on a time field
Posted: Tue Feb 10, 2004 6:38 am
by chulett
ray.wurlod wrote: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.
Re: OconvIconv on a time field
Posted: Tue Feb 10, 2004 7:40 am
by 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