Posted: Thu Jan 16, 2003 3:46 am Post subject: Decimal to date conversion in QMF
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?
Joined: 29 Nov 2002 Posts: 382 Topics: 27 Location: Chennai, India
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.
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 ...
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.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum