I am currently trying to put together a defined column to calculate the number of days between two dates. I have the following...

TESTING/I8=DATECVT(EDIT('01/01/2013','99$99$9999'),'A8MDYY','I8');
TESTING2/I8=DATECVT(EDIT('07/09/2013','99$99$9999'),'A8MDYY','I8');

DAYSAPART/I8=DATEDIF(TESTING,TESTING2,'D');

TESTING is coming back as 43772630
TESTING2 is coming back as 43974860
DAYSAPART is coming back as 202230

Obviously this is incorrect. Can someone please tell me what I am doing wrong here? I have tried this numerous different ways, and the final result is always the same (number of days * 1070).

Thanks,

Dan

DATEDIF uses full component date. Try:

TESTING/YYMD=DATECVT(EDIT('01/01/2013','99$99$9999'),'A8MDYY','YYMD');
TESTING2/YYMD=DATECVT(EDIT('07/09/2013','99$99$9999'),'A8MDYY','YYMD');
DAYSAPART/I8=DATEDIF(TESTING,TESTING2,'D');

I am still getting the same results using the following, which was suggested.

MINDATEFORYEARSTR/A20='01/01/2013';
TODAYYY/YY=&DATEYY;
MAXDATEFORYEARSTR/A20=IF TODAYYY EQ &SEPARATIONYEAR THEN TODAY('A10') ELSE '12/31/'|&SEPARATIONYEAR.EVAL;

TESTING/YYMD=DATECVT(EDIT(MINDATEFORYEARSTR,'99$99$9999'),'A8MDYY','YYMD');
TESTING2/YYMD=DATECVT(EDIT(MAXDATEFORYEARSTR,'99$99$9999'),'A8MDYY','YYMD');

DAYSAPART/I8=DATEDIF(TESTING,TESTING2,'D');

MINDATEFORYEARSTR - '01/01/2013'
MAXDATEFORYEARSTR - '07/09/2013'
TESTING - '2013/01/01'
TESTING2 - '2013/07/09'
DAYSAPART - 202230

Any other ideas?

Thanks!

quote:

MAXDATEFORYEARSTR/A20=IF TODAYYY EQ &SEPARATIONYEAR THEN TODAY('A10') ELSE '12/31/'|&SEPARATIONYEAR.EVAL;

[Edited] SEPARATIONYEAR is presumably a database variable, rather than a report parameter, so make that

MAXDATEFORYEARSTR/A20=IF SEPARATIONYEAR EQ TODAYYY THEN TODAY('A10') ELSE '12/31/' | SEPARATIONYEAR;

This message has been edited. Last edited by: j.gross,

Are the dates always strings, are they only date or date time ?

You should be able to convoert them the Smart dates, and then just subtract them to get the number of days.

Dan,
Your example implies that you are starting with date data stored as a string. On that basis I created the following test.
-* File test.fex
SET ONLINE-FMT=PDF
DEFINE FILE DATETIME
DATESTRING1/A10 = '01/01/2013' ;
DATESTRING2/A10 = '07/09/2013' ;
DATESTRING3/A8MDYY = EDIT(DATESTRING1,'99$99$9999') ;
DATESTRING4/A8MDYY = EDIT(DATESTRING2,'99$99$9999') ;
DATESMART_1/MDYY = DATESTRING3 ;
DATESMART_2/MDYY = DATESTRING4 ;
DAYSDIFF/I6 = DATESMART_2 - DATESMART_1 ;
END
TABLE FILE DATETIME
PRINT SD_MDY1 NOPRINT
DATESTRING1 IN +1 DATESTRING2 OVER
DATESTRING3 IN +1 DATESTRING4 OVER
DATESMART_1 IN +1 DATESMART_2 IN +2
DAYSDIFF IN +1
IF RECORDLIMIT EQ 1
END
-RUN
DATESTRING1 & 2 are simple string values.
DATESTRING3 & 4 convert these to legacy dates
DATESMART_1 & 2 convert the legacy dates to SmartDates
SmartDates are internally stored as days elapsed from 1901
DAYSDIFF simply subtracts to get the days between the dates (result is 189 days).
DATESTRING1 01/01/2013 DATESTRING2 07/09/2013
DATESTRING3 01/01/2013 DATESTRING4 07/09/2013
DATESMART_1 01/01/2013 DATESMART_2 07/09/2013 DAYSDIFF 189
The DTxxx and DAxxx functions can also be used to accomplish this. Given the rich set of date functions in FOCUS, I'm sure there are several other methods as well.
All of this and more can be found in:
(Almost) 1001 Ways to Work with Dates in WebFOCUS - available at www.Aviter.com.

This message has been edited. Last edited by: John W Price,