Hi All,

I've been searching forums all over the place to find an answer to this one. Does anyone know if there is a formula which will convert the IBM date format of CYYMMDD (bearing in mind this is an integer in the tables with no decimals) and converting it to a serial value date that excel will easily convert into any date format I could wish for?!

so for today the IBM date format is 1130312 (1 is the century, 13 the year, 03 the month and 12 the day). And the serial value in excel is 41345. I doubt there is such a magic formula but I can always live in Hope!

Thanks in advance

This message has been edited. Last edited by: Michael_86,

I'd convert it to a WebFOCUS date format (where the 1st "1" is "20") and use the Almost 1001 things you can do with dates...

Michael,
Try this:

  
DEFINE FILE CAR
IBM/I7=1130312;
CIBM/I8YYMD=19000000 + IBM;
FIBM/DMYY=CIBM;
END
TABLE FILE CAR
PRINT FIBM
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END

Hi Guys,

Thanks for the responses,

I also spoke to the support guys and their answer was to concat, cast and substr the date to create the output I want, not overly pretty but it works.

Danny, your code looks cool however im struggling to implement it as I write in SQL script as opposed to using WF. Is IBM meant to be a defined field? or is it created by me first?

Thanks

Michael,
Yes "IBM" is a DEFINEd field.
Pardon my saying so, but why write in SQL script? Let WF do the work instead of "concat, cast and substr"!
I only use SQL as a last resort.

Hi Danny,

Thanks i'll see if I can give it a try.

The reason I'm writing in SQL script is because I was trained in SQL in my last work, and I have never used WF before and have been unable to find a basic training guide for simple report writing. So using the knowledge I have and putting it to use in the best way I can.

Michael

SQL is fine to collect the data although webfocus is much easier
After the data collection you still need to write your report which is more then the data.
Or is somebody doing that in excel? Brrrr...

Michael,
Take a week's holiday over here: see the sights and learn WebFOCUS!

Frank - As I work in accounts all im really using this for is data mining, after extraction it is put into excel to populate our Financial Statements, and also I use it to pull out transactional data which I can then review and investigate easier in excel than in our accounting system (Good Old Chorus!)

Danny - You're on! if only I could get the holiday...

Ah, problems of time and distance!

Well, there is always "TeamViewer" for remote hands-on training and support...
...and remote vicarious tourism