I'm going crazy trying to compare a date stored in an amper variable with a normal date. I need to find out if the month and year are the same.
Here's the code to store the first date: [ TABLE FILE CIR_ACCOUNT_METRIC_400 SUM MAX.ACTL_DT ON TABLE HOLD AS HOLD_MAX_DATE FORMAT ALPHA END -RUN -READ HOLD_MAX_DATE &MAX_DATE.A8 -* This returns 04272009
DEFINE FILE CIR_BANKRUPTCY_METRIC Z_DAILYNEWIND/I1=IF ACTL_DT EQ &MAX_DATE THEN 1 ELSE 0; -* This works - it tells whether the dates are equal
DT_ACTL_DT/HYYMDm=HDTTM(ACTL_DT, 8, 'HYYMDm'); Z_MONTHLYNEWIND/D12.2=HDIFF(HDTTM(&MAX_DATE, 8, 'HYYMDm'),DT_ACTL_DT, 'MONTH','D12.2'); -* This fails - it gives answers like 139,057
]
I've tried using a combination of smart dates, HDIFF, HNAME, HDTTM and HINPUT with no success. Help!!This message has been edited. Last edited by: Kerry,
Version 769 Windows XP PDF
July 29, 2009, 01:13 PM
Glenda
Did you try dividing both dates by 100 and then comparing them?
Glenda
In FOCUS Since 1990 Production 8.2 Windows
July 29, 2009, 01:14 PM
Glenda
Of course, you will have to put them in I8YYMD format first.
Glenda
In FOCUS Since 1990 Production 8.2 Windows
July 29, 2009, 01:19 PM
Francis Mariani
If you need the number of months difference between two dates, you may not need to convert them to date-time, just to date, using the DATECVT function, then use DATEDIF to determine the number of units difference (M for Month):
-SET &MAX_DATE = '04272009';
TABLE FILE EMPLOYEE
PRINT FIRST_NAME
COMPUTE NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD');
COMPUTE NEW_DAT_INC/YYMD = DATECVT(&MAX_DATE, 'I8MDYY', 'YYMD');
COMPUTE MONTHS_HIRED/I8 = DATEDIF(NEW_HIRE_DATE, NEW_DAT_INC, 'M');
BY LAST_NAME
WHERE TOTAL MONTHS_HIRED NE 0;
WHERE DEPARTMENT EQ 'PRODUCTION';
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
July 29, 2009, 04:09 PM
DTM
I'd like to do this compare within the DEFINE statement so I can set the indicator for each row. I still haven't figured out how to convert both dates to I8YYMD within the DEFINE.
Version 769 Windows XP PDF
July 29, 2009, 04:14 PM
Francis Mariani
Why not like this:
-SET &MAX_DATE = '04272009';
DEFINE FILE EMPLOYEE
NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD');
NEW_DAT_INC/YYMD = DATECVT(&MAX_DATE, 'I8MDYY', 'YYMD');
MONTHS_HIRED/I8 = DATEDIF(NEW_HIRE_DATE, NEW_DAT_INC, 'M');
END
TABLE FILE EMPLOYEE
PRINT
FIRST_NAME
NEW_HIRE_DATE
NEW_DAT_INC
MONTHS_HIRED
BY LAST_NAME
WHERE MONTHS_HIRED NE 0;
WHERE DEPARTMENT EQ 'PRODUCTION';
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
July 29, 2009, 04:36 PM
Edward Wolfgram
Something like this should work for you:
-SET &MAXDATE = 04272009 ;
DEFINE FILE CAR
CTR/I1 WITH COUNTRY = LAST CTR + 1 ;
ACTL/I8MDYY = DECODE CTR (1 01012009 2 02022009 3 03032008 ELSE 0);
MYI/I8MDYY WITH COUNTRY = &MAXDATE ;
MYMAX/YYM = MYI ;
MYB/YYM = ACTL ;
DIFF/I5 = MYMAX - MYB ;
END
TABLE FILE CAR
PRINT ACTL MYI MYMAX MYB DIFF
IF RECORDLIMIT EQ 3
END
-RUN
PAGE 1
ACTL MYI MYMAX MYB DIFF
---- --- ----- --- ----
01/01/2009 04/27/2009 2009/04 2009/01 3
02/02/2009 04/27/2009 2009/04 2009/02 2
03/03/2008 04/27/2009 2009/04 2008/03 13
IBI Development
July 29, 2009, 05:20 PM
DTM
Francis the DATEDIF function won't work for me because I need to know if the months are the same not if the difference is less than a month. I did use your code to convert my amper date.
Here's how I got it to work using HNAME: [ -SET &MAX_DATE = '04272009';
Edward, I didn't get a chance to try your solution.
Thanks everyone for your help.
Version 769 Windows XP PDF
July 30, 2009, 08:01 PM
Doug
Here's a freebee to try on for size:
-* File date2variabl_comp.fex
-SET &VAR_DATE = '011982';
DEFINE FILE EMPLOYEE
VAR_DATE_MNYY/MYY = &VAR_DATE;
HIR_DATE_MNYY/MYY = HIRE_DATE ;
SAME_MONTH/A3 = IF VAR_DATE_MNYY EQ HIR_DATE_MNYY THEN 'YES' ELSE 'no' ;
END
TABLE FILE EMPLOYEE
PRINT
VAR_DATE_MNYY AS 'YYYY/MM,Check'
HIR_DATE_MNYY AS 'Hired,Mo/Year'
SAME_MONTH AS 'Hired,During,&VAR_DATE'
BY FIRST_NAME AS 'First Name'
BY LAST_NAME AS 'Last Name'
-*Use this as required: WHERE SAME_MONTH EQ 'YES';
HEADING CENTER
"These employees were hired during <VAR_DATE_MNYY"
WHERE DEPARTMENT EQ 'PRODUCTION';
ON TABLE SET PAGE OFF
END
Edit out the "27" from what your -READ in (-READ HOLD_MAX_DATE &MAX_DATE.A8 -* This returns 04272009, then EDIT &MAX_DATE as required to get only the 04/2009)