MVSFORUMS.com

bindu · Beginner Joined: 05 Jan 2003 Posts: 17 Topics: 7

Hello,
I have a date data stored in DB2 tables as decimal data type.But while reporting i need to format the data in the date fmt.ie my data in DB2 tables is as follows:
000118
But i want it in the following format in the report:
00/01/18
Any way of doing this in QMF?

Regards,
Bindu

Manas Biswal · Posted: Thu Jan 16, 2003 6:27 am Post subject:

You can use edit codes in the FORM section of QMF after running your query. But I think a date format edit code will not be alllowed for a numeric data type. If you are exporting your report to a dataset, then you can always do whatever formatting you like in your dataset.

Regards,
Manas

kolusu · Posted: Thu Jan 16, 2003 7:05 am Post subject:

Bindu,
You can use the DIGITS function to get the desired results. I assumed that your column is defined as decimal 6 .try the following sql

bindu · Beginner Joined: 05 Jan 2003 Posts: 17 Topics: 7

Thanks .. It worked .. The method we tried earlier was to give the rexx function in the field definition of our qmf form.
The procedure we tried is as follows:
We retrieved the data as scuh from the db2 tables.the data we got was 118.Then we defined another field which will pad the data for zeroese.The field definition for this was written as follows:
RIGHT(&1,6,0) ..&1 is the id of the colomn which contains raw data from the table.

Then we defined another field to format the date and wrote the field definition for this as follows:
SUBSTR(&3,1,2)||'/'||INSERT('/',SUBSTR(&3,3,6),2) -- &3 is the field which contains date with proper data of length 6.

This final field will be displayed in our report.

This is a much round about method .

The method suggested by you simplified our tasks a lot.

Thanks for the solution ...
Smile

But when we gave the query as such as suggested by you the result we got was as follows:

00/00/11

But when we gave
SELECT
SUBSTR(DIGITS(columnname),2,2)
CONCAT '/' CONCAT
SUBSTR(DIGITS(columnnameT),4,2)
CONCAT '/' CONCAT
SUBSTR(DIGITS(columnname),6,2)
AS SYSTEM_DATE
FROM table name
we got the desired result
ie
00/01/18
This should be because our field was defined to be of decimal of length 7 and not 6.

Thanks a lot for the suggestions

Regards,

Bindu