Date Difference

November 14, 2006, 04:27 PM

moyer1dl

Date Difference

I have a 7 digit alphanumeric date (ex. 2040505).

I want to subtract this date from the current date.

I am having trouble with the datedif function, I cant seem to get the right syntax on this.

We are using WF7.1.1 with VSAM files.

Any

Help

Dan

November 14, 2006, 04:36 PM

Francis Mariani

14 November, 2006:

20061114, 14112006, 11142006 is 8.
061114 , 141106 , 111406 is 6.

What's a 7 digit alphanumeric date? Do you mean a Julian Date?

I think you would need to convert to Gregorian before doing anything further with the date:

COMPUTE GREG_DATE/I8 = GREGDT(JULIAN, 'I8');

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

November 14, 2006, 04:41 PM

moyer1dl

From my understanding it was a y2k issue

204 year 05month 05day this how you would read it.

November 14, 2006, 04:52 PM

Francis Mariani

As far as I can recall (it was so long ago!) the Y2K issue was that the century was missing, hence a 6 digit date. If you need to insert a zero to make it an 8 digit date, you could do this:

COMPUTE DATE2/A8YYMD =EDIT(DATE1,'90999999');

An example that might work for you:

TABLE FILE CAR
PRINT 
COUNTRY
COMPUTE TODAY/YYMD= '&YYMD';
COMPUTE DATE1/A7 = '2040505';
COMPUTE DATE2/A8YYMD =EDIT(DATE1,'90999999');
COMPUTE DATE3/YYMD = DATE2;
COMPUTE DIFF/I4 = DATEDIF(DATE3, TODAY, 'D');
END

DATE1 is your date. The D in the DATEDIF function provides the resulting difference in days. It could be Y for years or M for months.

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

November 14, 2006, 04:54 PM

Francis Mariani

From the docs:

quote:
Syntax: How to Find the Difference Between Two Dates
DATEDIF(from_date, to_date, 'unit'[, outfield])where:

from_date

Date

Is the start date from which to calculate the difference.

to_date

Date

Is the end date from which to calculate the difference.

unit

Alphanumeric

Is one of the following enclosed in single quotation marks:

Y indicates a year unit.

M indicates a month unit.

D indicates a day unit.

WD indicates a weekday unit.

BD indicates a business day unit.

outfield

Alphanumeric

Is the field that contains the result. This value is required only for Maintain.

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

November 22, 2006, 03:00 PM

oceannative

quote:
Originally posted by Francis Mariani:
14 November, 2006:

20061114, 14112006, 11142006 is 8.
061114 , 141106 , 111406 is 6.

What's a 7 digit alphanumeric date? Do you mean a Julian Date?

I think you would need to convert to Gregorian before doing anything further with the date:

COMPUTE GREG_DATE/I8 = GREGDT(JULIAN, 'I8');

I have an alphanumeric field that contains a Julian date. I want to convert it to a Gregorian date field. I used this compute, but it does not appear to do the conversion correctly... heres my compute: COMPUTE C_CLDATE2/I8 = GREGDT(QAF0401.QAF0401.A6URAB,'I8');

Before the compute: 106334
After the compute: 1061130

What am I missing?

November 22, 2006, 05:41 PM

Francis Mariani

TABLE FILE CAR
PRINT
COMPUTE JULDT/I7 = 2001203;
COMPUTE GREG_DATE/I8 = GREGDT(JULDT, 'I8');
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
END

Will give you the correct conversion when the Julian date you're trying to convert is correct.

The GREGDT function operates with Julian dates (two or four digit year and the number of days within that year, ie. 2001203 is 2001/07/22).

The examples you've provided are not valid Julian dates, eg. 2040505 - more than 366 days within year 2040, 106334 - not seven digits.

Check this out:
http://whatis.techtarget.com/definition/0,,sid9_gci212429,00.html

Particularly, "Commonly in computer programming, Julian date has been corrupted to mean the number of elapsed days since the beginning of a particular year. For example, in this usage, the Julian date for the calendar date of 1998-02-28 would be day 59."

Perhaps your dates are stored in some other format?

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

November 24, 2006, 11:07 AM

oceannative

Yes, I did more research and found that the JDE version of the Julian date is 1st digit=century, 2nd/3rd=year, and 4-6 is the day #. As a workaround, I was able to join to a date logical so I can bring in the field as MDY, so now I'm researching to figure out how to make that a date field. When I change the format, it doesn't work, so I'm thinking I need to convert it to a DATE first. Thank you for the feedback...

November 27, 2006, 10:10 AM

Francis Mariani

quote:
1st digit=century, 2nd/3rd=year, and 4-6 is the day #

If you now know the format of the alpha field, you can insert the missing numeral to create a new alpha field and then convert that into a date field.

As per a previous example:

COMPUTE DATE1/A7 = '2040505';
COMPUTE DATE2/A8YYMD =EDIT(DATE1,'90999999');

But I'm still not sure how your second example, 106334, fits into the rule.

Cheers,

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

February 05, 2007, 11:09 AM

moyer1dl

Thanks Francis your suggestion fixed my problem

Dan

February 05, 2007, 02:11 PM

Francis Mariani

Thank you for your belated thank you

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