Transform DATE OCONV() Question

ArndW · Post by **ArndW** » Tue Mar 01, 2005 4:58 am

Even though I thought I could do OCONVs and ICONVs in my sleep, I just got a rather simple OCONV to do, and I can't for the life of me figure out the syntax.

I would like to have OCONV output today's date in the form YYYYMMDD with no separators.

If I use OCONV(@DATE,"D4YMD") it will use a " " (space) as the separator. I've tried a couple of permutations of the "D[...]" options to no avail.

I know I can do a REPLACE(' ','',OCONV(@DATE,"D4YMD[4,2,2]")) but I would expect that with the complexity of the date formats available that there is a simple OCONV solution.

Any solutions out there?

roy · Post by **roy** » Tue Mar 01, 2005 6:26 am

Hi,
AFAIK:
unfortunatly the only solution is to strip the blanks from the date you get, no syntax of Oconv generates YMD with no seperators at all.

there are several functions you can use to acieve this, one of them you already stated.

I wish I had better news

,

ArndW · Post by **ArndW** » Tue Mar 01, 2005 6:35 am

Roy,

I was sort of hoping that someone would confirm that - I really did search all around for an answer. But since I spent one hour today trying to get a a simple SQL insert to run and even though the error message told me "failed to insert on column PARM" I couldn't find the error - (and my column name was PARAM) I though it might be best to get a second or third opinion

Thanks,

kcbland · Post by **kcbland** » Tue Mar 01, 2005 6:37 am

AFAIK, there's no switch, but Ray hasn't spoken yet. Consider using a master set of parameters for all jobs, including ones like a sysdate or processdate. Derive it once in the job control and pass it to all jobs preformatted, that way you don't have to OCONV+CHANGE everytime you access @DATE.

ArndW · Post by **ArndW** » Tue Mar 01, 2005 7:11 am

Ken,

my problem is that I'm writing a complex set of job control for this project. The parameter names and values are stored in a file/table, I read these and then have to write my own parsing to replace placeholders in some of those values, one of which is "@TODAY{dateformat}@"; and the dateformat contains the normal OCONV format - except for some of the jobs we need a format without the date separators. So what I've now done is program some explicit exceptions to the formatting rules - but it would have been nicer to just use the OCONV format without exceptions.

I wish I still had the sources and could check the formatting code myself... one of the drawbacks of not being with the company anymore.

Perhaps someone can think of an OCONV formatting code to prove us all wrong. I'd promise Ray a drink if he knows the answer, but we all know that he doesn't use profanity, doesn't drink, doesn't smoke and doesn't play baseball...

ArndW · Post by **ArndW** » Tue Mar 01, 2005 11:42 am

Ken,

the memory just came back to me - I remember rooting around in the source code for the conversion format parsing a couple of years back for the same reason, to try to find out if I can get a date outputted without a separator. I didn't find one but did modify it to allow a null output and put that into the change pipeline (this was for both UniVerse & DS) but I guess it never got implemented. Strange how the memory can be elusive and then suddenly come back clearly - I now vividly remember that trip to Ernie Piper's office and my discussions regarding this issue!

naren6876 · Post by **naren6876** » Tue Mar 01, 2005 11:53 am

Following will Solve your problem

EREPLACE(OCONV(@DATE,"DYMD[4,2,2]"),"/","")

ArndW · Post by **ArndW** » Tue Mar 01, 2005 12:04 pm

Naren,

thanks - that's actually what I put into my initial query

[If I said that "great minds think alike" the two of us would probably get blackballed from this forum and have to switch to using the I-tool

]

ray.wurlod · Post by **ray.wurlod** » Tue Mar 01, 2005 3:12 pm

I prefer Oconv(@DATE, "DYMD[4,2,2]" : @VM : "MCN") as I said very recently.

Arguably one should be able to use "" as the separator character, for example Oconv(@DATE, "D''YMD[4,2,2]"), but this does not work.

kommven · Post by **kommven** » Wed Mar 02, 2005 7:34 am

I got the same situation and I used Trim("XXXX XX XX", " ", "A")

try it and let us know...

ArndW · Post by **ArndW** » Wed Mar 02, 2005 8:33 am

Kommven - I like your solution best; of the 4 different methods proposed so far the TRIM "A" is the most efficient

Thanks,

-Arnd.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 02, 2005 4:12 pm

... but introduces a Y10K problem and doesn't handle BC dates

ArndW · Post by **ArndW** » Thu Mar 03, 2005 3:06 am

I was curious as to performance of the different approaches to modifying the OCONV date into a YYYMMDD format and did some empirical tests; running the different solutions for a long time to get comparable results; the one that I thought would work quickest (the 2-statement substring one) ended up being the slowest. The results are in milliseconds of CPU time, all measured in the same process; so they are both representative and comparable. It should be noted that the OCONV function shared by all the calls takes the largest proportionate amount of CPU time.

Results
72680ms for "TRIM(OCONV(@DATE,'D4/YMD[4,2,2]'),'/','A')"
73230ms for "TRIM(OCONV(@DATE,'D4YMD[4,2,2]'),' ','A')"
74710ms for "TRIM(OCONV(@DATE,'D4YMD[4,2,2]'),'A')"
74710ms for "CONVERT(' ','',OCONV(@DATE,'D4YMD[4,2,2]'))"
83680ms for "EREPLACE(OCONV(@DATE,'D4/YMD[4,2,2]'),'/','')"
88530ms for "z = OCONV(@DATE,'D4/YMD[4,2,2]')"
"z = z[1,4]:z[6,2]:z[9,2]"

kommven · Post by **kommven** » Thu Mar 03, 2005 9:15 am

Try simply
"TRIM(OCONV(@DATE,'D YMD[4,2,2]'),' ','A')"

ArndW · Post by **ArndW** » Thu Mar 03, 2005 9:36 am

Kommven - why? The only difference is in the date conversion, which I kept the same for all of the trials. I just tested it and it runs in the same cpu-ticks as it's MD4 counterpart.