I know there are a lot of discussions on this, but i haven't found dates the way they are in our system.
Our date is stored into an P9 YYYYMMDD, i would like to subtract that date from current date to get number of days, and also from another date in the same record.
Also, I'm looking to add current date to the WHERE statement in the P9 format.
Please Help.This message has been edited. Last edited by: Joe Beydoun,
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
May 24, 2011, 02:46 PM
Charles Richards
quote:
Our date is stored into an P9 YYYYMMDD, i would like to subtract that date from current date to get number of days, and also from another date in the same record.
Also, I'm looking to add current date to the WHERE statement in the P9 format.
Please Help.
use the Funtion AYMD (YYYYMMDD, amount of days, 'YYYYMMDD')
WebFOCUS 7.6 Windows, All Outputs
May 24, 2011, 04:31 PM
Joe Beydoun
How do i subtract two P9 dates or one P9 date from current date.
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
May 24, 2011, 04:38 PM
Dan Satchell
One method is convert your dates to SmartDates so you can add and subtract the dates with simple arithmetic.
1. I need Current date in a -SET statement to look like 05/24/2011.
2. I need to extract the time from a P15 field (date/time) that looks like this 20110524171520, I tried an edit but i get a goofy number.
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
May 24, 2011, 06:06 PM
Waz
What do you want to do with the time ?
As a text field, you could use PTOA, then EDIT with a MASK
If its needed in a Datetime field, then you will need one of the H??? functions.
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!
May 24, 2011, 06:12 PM
Joe Beydoun
Just need it as a text for now.
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
May 24, 2011, 07:09 PM
Dan Satchell
For date in mm/dd/yyyy format try:
-SET &DATEVAR = &DATEMDYY ;
Regarding conversion of your numeric date-time to alphanumeric, try function FPRINT. Search this forum for FPRINT. The most recent entry will give you the syntax for FPRINT (which is apparently mostly undocumented!). After conversion to alphanumeric, you can format it with the EDIT function.
WebFOCUS 7.7.05
May 24, 2011, 10:12 PM
jimster06
May I recommend the following:
(Almost) 1001 Ways to Work With Dates for WebFOCUS This reference manual explains the date-related functions of WebFOCUS as well as the several, different types of dates supported by WebFOCUS. The specific functions and methods used vary depending on the type of date involved. Preview a sample of (Almost) 1001 Ways to Work With Dates for WebFOCUS.
jimster06 DevStu WF 7.6.11 W7 HTML, PDF, EXL2K
May 25, 2011, 01:18 PM
Tony A
Just as Waz suggests, use a combination of PTOA to transfer your Packed decimal to Alpha and then use HINPUT -
DEFINE FILE CAR
INPUTDT/P15 = 20110524171520;
INPUTDTA/A17 = PTOA(INPUTDT, '(P15)', 'A17');
DT_FROM_ALPHA/HYYMDS = HINPUT(17, INPUTDTA, 8, 'HYYMDS');
END
TABLE FILE CAR
PRINT INPUTDT
INPUTDTH
DT_FROM_ALPHA
BY COUNTRY
IF RECORDLIMIT EQ 1
END
You can then use functions such as HPART to get the components, HDIFF to get differences for various components (Months, Days, Weeks etc.)
To get the current date into datetime format you can just use the same sort of DEFINE using a variable such as - -SET &TodayH = &YYMD || EDIT(&TOD,'99$99$99');
TThis message has been edited. Last edited by: Tony A,
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
May 31, 2011, 12:29 PM
Edward Wolfgram
Joe, your basic calculations can be done as follows:
DEFINE FILE CAR
MYP/P9 WITH COUNTRY = 20110429 ;
MYBIG/P16 WITH COUNTRY = 20110524171520 ;
MYPD/P8YYMD = MYP ;
CURRD/I8YYMD WITH COUNTRY = &YYMD ;
MYNEWD/YYMD = MYPD ;
MYCURR/YYMD = CURRD ;
MYDIFF/I6 = MYCURR - MYNEWD ;
MYBDATE/P8 = MYBIG/1000000 ;
MYTIME/P6 = MYBIG - (MYBDATE*1000000) ;
END
TABLE FILE CAR
PRINT MYP MYBIG MYBDATE MYTIME MYDIFF
END
Also, remember that you can directly get alpha from packed using EDIT- e.g.
MYA/A6 = EDIT(MYTIME) ;
IBI Development
May 31, 2011, 12:38 PM
Joe Beydoun
Thanks Everyone, I used everything I got here and was able to accomplish all requirements.
Much Appreciated!!!
Only thing is, the EDIT did not work to extract packed to alpha, had to use PTOA.
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.