I want to use the DATETRAN function to format it in different ways in different langauges. The format and language will be passed in as variables. Sometimes I will display the full date (different combinations of DMY), and othertimes I will display only the month (different combinations of MY).
The code below illustrates my problem. I was hoping to use the one DEPARTUREDATE. To my dismay, DATE2 is not correct when I used DEPARTUREDATE with '(MYY)'.
Why? It seems I have to redefine the DEPARTUREDATE into a new field of YYM before I can use '(MYY)' in the DATETRAN. Shouldn't the date be stored internally the same way, regardless of how I want to print it out?
I'm trying to understand this as I need to suggest how we are going to handle this in all of our reports. Do I really have to add the extra logic to redefine the date to something like MYY if there is not a D in the format? Or is there something I can do in the masterfile to avoid this extra step?
DEFINE FILE CAR DEPARTUREDATE/YYMD = '2009-01-15'; DATE1/A20 = DATETRAN(DEPARTUREDATE,'(DMY)','(-dt)','EN',20,'A20'); -*Incorrect DATE2/A8 = DATETRAN(DEPARTUREDATE,'(MYY)','(-t)','EN',8,'A8'); -*Extra step needed to display as month and year DEPARTUREMONTH/YYM = DEPARTUREDATE; DATE3/A8 = DATETRAN(DEPARTUREMONTH,'(MYY)','(-t)','EN',8,'A8'); END TABLE FILE CAR PRINT COUNTRY DATE1 DATE2 DATE3 ENDThis message has been edited. Last edited by: Kerry,
Judy Miller Software Engr, Decision Support Prism Group, Inc. Email: judy@prism-grp.com
WebFOCUS 7.6.9 Windows XP Output: PDF, Excel, COMT
December 21, 2009, 10:23 PM
Waz
I would suggest putting in case with IBI to get this fixed.
It certianly looks like a Bug.
Also if you are converting to Dutch, the month names do not work for 7.6.4 or 7.6.5. Don't know about later versions.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 21, 2009, 11:57 PM
Francis Mariani
Usually, if you have a DATE formatted column, you don't have to use any functions to change the format - you should be able to change the format in the TABLE FILE PRINT/SUM:
DEFINE FILE CAR
DEPARTUREDATE/YYMD = '2009-01-15';
END
TABLE FILE CAR
PRINT
COUNTRY
DEPARTUREDATE
DEPARTUREDATE/YYM
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 22, 2009, 01:01 AM
Danny-SRL
Judy,
As you know, Focus dates are stored as the number of days since 01/01/1901.
However, when you have a date of the form year-month, then, if I am not mistaken, the date is stored as the number of months since 01/1901. Hence your (strange) result.
If your master file maps an RDBMS table, then I suggest you try the following:
By the way, do you have to create multilingual reports from a single database? If so, we have a lot of experience on this side of the world which I can share, if you wish.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
December 22, 2009, 05:40 AM
Dan Satchell
The documentation for DATETRAN is confusing. It says the second parameter defines the input format, but in fact it defines the output format. Since there is no parameter to describe the input format, I suspect the input must already be in a format compatible with the requested output. In your case, if you want MYY output, the input may need to be in some form of year-month or month-year, as you discovered. And as Waz suggested, it might be worth the trouble of opening a case to see what IBI has to say about it.This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
December 22, 2009, 06:19 PM
JudyM
Thank you for your replies. [SOLVED]
Judy Miller Software Engr, Decision Support Prism Group, Inc. Email: judy@prism-grp.com
WebFOCUS 7.6.9 Windows XP Output: PDF, Excel, COMT