[SOLVED] Date Agony

I'm stuck on date agony.

I have 3 fields: year/i4 month/i2 and day/i2.

How would you convert these fields into a single date string in yymd format?

Thank you

This message has been edited. Last edited by: FP Mod Chuck,

Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36

Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006

BabakNYC

Virtuoso

posted

Hide Post

FULL_DATE/A8YYMD=EDIT(YEAR)|EDIT(MONTH)|EDIT(DAY);

WebFOCUS 8206, Unix, Windows

Posts: 1853 | Location: New York City | Registered: December 30, 2015

Ignored post by BabakNYC posted

Show Post

MartinY

Virtuoso

posted

Hide Post

FULL_DATE/I8YYMD=YEAR * 10000 + MONTH * 100 + DAY;

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Show Post

jgelona

Master

posted

Hide Post

Expanding on MartinY's answer. To get a smart date:

FULLDATE/YYMD=DATECVT(YEAR*10000+MONTH*100+DAY,'I8YYMD','YYMD');

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006

Ignored post by jgelona posted

Show Post

Hallway

Master

posted

Hide Post

Expanding on BabakNYC's answer. To get a smart date:

 DATE_YYMD/YYMD = DATECVT(EDIT(YEAR)|EDIT(MONTH)|EDIT(DAY),'A8YYMD','YYMD');

Smart dates are actually pretty easy to work with once you know that the date is just an integer that is an offset of the number of days since 12/31/1900. This means that the integer 1 = 1/1/1901, 2 = 1/2/1901, 43037 = 10/30/2018, etc.

Incrementing smart dates is really easy. All you need to do is add or subtract the number of days you need. Run the fex below to see some examples of different date formats.

  
DEFINE FILE CAR
YEAR/I4  = DTPART('&YYMD', YEAR);
MONTH/I2 = DTPART('&YYMD', MONTH);
DAY/I2   = DTPART('&YYMD', DAY);

-*ALPHA CONVERSION 
DATE1_A8/A8 = EDIT(YEAR)|EDIT(MONTH)|EDIT(DAY);
DATE1_A8YYMD/A8YYMD = EDIT(YEAR)|EDIT(MONTH)|EDIT(DAY);
DATE1_YYMD/YYMD = DATECVT(EDIT(YEAR)|EDIT(MONTH)|EDIT(DAY),'A8YYMD','YYMD'); -* <-- SMART DATE

-*INTEGER CONVERSION 
DATE2_I8/I8 = YEAR * 10000 + MONTH * 100 + DAY;
DATE2_I8YYMD/I8YYMD = YEAR * 10000 + MONTH * 100 + DAY;
DATE2_YYMD/YYMD = DATECVT(YEAR * 10000 + MONTH * 100 + DAY,'I8YYMD','YYMD'); -* <-- SMART DATE

SMART_DATE_DAY1/I1 = 1;
BREAK/A15 = 'Converted Date'; 
END
 
TABLE FILE CAR
PRINT YEAR
OVER MONTH
OVER DAY

OVER BREAK AS 'Alpha Conversion'
OVER DATE1_A8
OVER DATE1_A8YYMD
OVER DATE1_YYMD

OVER BREAK AS 'Integer Conversion'
OVER DATE2_I8
OVER DATE2_I8YYMD
OVER DATE2_YYMD

OVER COMPUTE SDATE/A50V = 'Smart<br>Date'; AS 'Smart Date<br>Examples' 
     COMPUTE DESC/A50V = '# Days After<br>12/31/1900'; AS ''
OVER SMART_DATE_DAY1/YYMD AS 'Smart Date Day 1'
     SMART_DATE_DAY1/I1 AS ''
OVER DATE2_YYMD/YYMD AS 'Today''s Date'
     DATE2_YYMD/I11 AS '' 
OVER COMPUTE DATE_YYMD_PLUS7/YYMD = DATE2_YYMD + 7; AS 'Today''s Date + 7'
     COMPUTE DATE_YYMD_PLUS7/I11  = DATE2_YYMD + 7; AS ''
BY COUNTRY NOPRINT
HEADING
"Today's Date: &DATEMDYY"
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, FONT=CALIBRI, GRID=OFF, $
TYPE=TITLE, COLUMN=P4,  BACKCOLOR=SILVER, STYLE=Bold, $
TYPE=DATA , COLUMN=P4,  BACKCOLOR=SILVER, STYLE=Bold, $
TYPE=TITLE, COLUMN=P8,  BACKCOLOR=SILVER, STYLE=Bold, $
TYPE=DATA , COLUMN=P8,  BACKCOLOR=SILVER, STYLE=Bold, $
TYPE=TITLE, COLUMN=P12, BACKCOLOR=SILVER, STYLE=Bold, $
TYPE=DATA , COLUMN=P12, BACKCOLOR=SILVER, STYLE=Bold, $
TYPE=DATA , COLUMN=P13, BACKCOLOR=SILVER, STYLE=Bold, $
TYPE=HEADING, STYLE=Bold, $
ENDSTYLE
END

-HTMLFORM BEGIN 
<style>
    table {border-collapse: collapse; }
</style>
-HTMLFORM END

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