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,

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')

How do i subtract two P9 dates or one P9 date from current date.

One method is convert your dates to SmartDates so you can add and subtract the dates with simple arithmetic.

DEFINE FILE ...
 DATE1/I8YYMD = YOUR_P9_DATE ;
 DATE2/YYMD   = DATE1 ;
 CURDATE/YYMD = '&YYMD';
 DATEDIFF/I5  = CURDATE - DATE2 ;
END

Thanks Dan, that was very helpful.

Couple more things now:

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.

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.

Just need it as a text for now.

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.

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.

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');

T

This message has been edited. Last edited by: Tony A,

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) ; 

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.