[SOLVED] Converting date to Jan-14 (Mtr)

Focal Point

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1667008276

September 24, 2014, 11:58 AM

FOCdeveloper

[SOLVED] Converting date to Jan-14 (Mtr)

Hi All.. I have a DATEing issue
I have two fields
YR/A2='14';
MO/A2='01';
Need to Display as Jan-14
I can convert MO to Jan using Mtr format..
Then I would like to concatenate Month (Jan) with - to Year .. Like Jan-14, Feb-14 etc..
WF does not convert Mtr Mon to Alpha and does not concatenate .. gives format error..

How Do I convert Mtr Month (Example: Jan) to Character field so that I concatenate with '-' and Year..

Thanks

This message has been edited. Last edited by: <Kathryn Henning>,

Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7

September 24, 2014, 01:08 PM

Francis Mariani

The proper answer would be to convert your data to a date column and then use the DATETRAN function to get YY-Mt but because I believe DATETRAN is quite tedious to use, I would user the non-proper solution and compute the three character name:

TABLE FILE CAR
PRINT
COMPUTE YR/A2='14';
COMPUTE MO/A2='01';

COMPUTE MOTr/A3 = DECODE MO ('01' 'Jan', '02' 'Feb', '03' 'Mar', '04' 'Apr', '05' 'May', '06' 'Jun'
                             '07' 'Jul', '08' 'Aug', '09' 'Sep', '10' 'Oct', '11' 'Nov', '12' 'Dec' ELSE '???');

COMPUTE MOTr_YY/A6 = MOTr | '-' | YR;
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
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

September 24, 2014, 02:15 PM

Shakila Subhan

using DATETRAN:

DEFINE FILE GGSALES
 Date1/YYMD=DATE;
Date1A/MYY = Date1;
Date2/A40=DATETRAN(Date1A,  '(MY)', '(-t)', 'EN', 40, 'A40');
 END
 TABLE FILE GGSALES
 SUM DOLLARS
 BY Date1A NOPRINT
BY Date2
  END
-RUN

September 24, 2014, 02:26 PM

MartinY

Using alpha information as you provide :

DEFINE FILE CAR
YR     /A2  = '14';
MO     /A2  = '01';
YRMODY /A4YM = YR | MO;
DTE /A20 = DATETRAN(DATECVT(YRMODY, 'A4YM','MY'), '(MY)', '(-t)', 'EN', 20, 'A20') ;
END
TABLE FILE CAR
PRINT 
DTE
BY COUNTRY
WHERE RECORDLIMIT EQ 1;
WHERE READLIMIT EQ 1;
END

Also work with numeric value

DEFINE FILE CAR
YR     /I2  = 14;
MO     /I2  = 01;
YRMODY /I4YM = YR * 100 + MO;
DTE /A20 = DATETRAN(DATECVT(YRMODY, 'I4YM','MY'), '(MY)', '(-t)', 'EN', 20, 'A20') ;
END
TABLE FILE CAR
PRINT 
DTE
BY COUNTRY
WHERE RECORDLIMIT EQ 1;
WHERE READLIMIT EQ 1;
END

September 24, 2014, 02:29 PM

Alan B

Unlike Francis I like DATETRAN, and for full detail:

DEFINE FILE CAR
YR/A2='14';
MO/A2='01';
I_YM/I4YM = EDIT(YR | MO);
D_MY/MY = I_YM;
A_MY/A9 = DATETRAN(D_MY, '(MY)', '(t-)', 'EN', 6, 'A6');
END
TABLE FILE CAR
PRINT YR MO I_YM D_MY A_MY
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
END

September 24, 2014, 02:32 PM

FOCdeveloper

Thank you Francis and Shakila..
After posting this topic.. I decided to just use the DECODE.. as Francis suggested

I tried DATETRAN method also as it was posted here before.. but after going thru all the hoops, it returned 10-12, 11-12 etc

I think editing Mt month to Char. would be the smart way to go.. But IBI does think so..

Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7

September 24, 2014, 02:37 PM

MartinY

From a multi language pov, using DATETRAN is a better solution because you can pass the language as a parameter in the function and have the month in the proper description :

quote:
DTE /A20 = DATETRAN(DATECVT(YRMODY, 'I4YM','MY'), '(MY)', '(-t)', '&LANG.EVAL', 20, 'A20') ;

September 24, 2014, 05:09 PM

FOCdeveloper

Thanks Alan and MartinY

DATETRAN method worked.. but if the Mo and Yr are blank (no data), it comes up with Dec-00..

in the DECODE, I have it 'N/A'
MON1/A4= DECODE GL_MO ('01' 'Jan-' '02' 'Feb-' '03' 'Mar-' '04' 'Apr-' '05' 'May-' '06' 'Jun-' '07' 'Jul-' '08' 'Aug-' '09' 'Sep-' '10' 'Oct-' '11' 'Nov-' '12' 'Dec-' ELSE 'N/A');

PERIOD1/A6= MON1|EDIT(YR1,'$$99');
..

Thanks All

Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7

September 24, 2014, 05:16 PM

Alan B

DEFINE FILE CAR
YR/A2='14';
MO/A2=' ';
I_YM/I4YM = EDIT(YR | MO);
D_MY/MY = I_YM;
A_MY/A9 MISSING ON= IF DATETRAN(D_MY, '(MY)', '(t-)', 'EN', 6, 'A6') EQ 'Dec-00' THEN MISSING ELSE DATETRAN(D_MY, '(MY)', '(t-)', 'EN', 6, 'A6') ;

END
TABLE FILE CAR
PRINT YR MO I_YM D_MY A_MY
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE SET NODATA 'N/A'
END

September 24, 2014, 05:37 PM

FOCdeveloper

Thanks Alan.. It worked..

Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7

September 25, 2014, 10:38 AM

DavSmith

Using FPRINT is another option:

Dialogue Manager

-SET &MT=03;
-SET &YR=14;
-SET &FDATE=IF &MT LT 1 OR &MT GT 12 THEN 'N/A' ELSE 
-           IF &YR LT 1 OR &YR GT 99 THEN 'N/A' ELSE EDIT(FPRINT(&MT.EVAL&YR.EVAL,'I4MTY','A6'),'999$-99');
-TYPE &FDATE

Focus Reporting Language

TABLE FILE CAR
SUM COUNTRY NOPRINT
COMPUTE MT/A2='02';
COMPUTE YR/A2='14';
COMPUTE FDATE/A6=IF EDIT(MT) LT 1 OR EDIT(MT) GT 12 THEN 'N/A' ELSE 
                 IF EDIT(YR) LT 1 OR EDIT(YR) GT 99 THEN 'N/A' ELSE EDIT(FPRINT(MT|YR,'A4MTY',FDATE),'999$-99');
END