The Director's Chair
by Noreen Redden		FOCUS 101
		Feature
		From CSS
		Advanced Technique
		Summer Olympics Challenge
		New Feature Part 2
		Releases Currently Shipping
		User Group Meeting Schedule

Using Date-Related Subroutines This is actually the first of a multipart series of articles dealing with subroutines, how to use them, and some "gotchas" along the way. This first article mainly concerns subroutines dealing with Old and New Dates. Future articles will deal with date-time fields and other subroutines that may prove useful to you. First, a little background on dates. In the original FOCUS, dates were essentially strings of digits that represented Month, Day, and Year in the sequence of your choice. They could be stored as Alpha, Packed, or Integer, and had formats like A6YMD or I8YYMD or P6MTDYY. In all cases, there were six or eight digits, where YY indicated a four-digit year, including century, Y only the last two digits of the year, M for month, and D for Day. The T indicated whether or not to translate the month into an alpha, but was for display only. Also, these so-called "traditional" dates did no validation that month was 1-12, Day was a reasonable day for the given Month, etc. This is not necessarily a negative, as some systems were actually established with 13-month periods for tracking money received after the end of a given year, but applicable to that year. Similarly, every month could have 31 days in order to facilitate scheduling, etc. However, because these dates were only strings of digits, you could not easily subtract two dates, for instance, as 20040815 ­ 20040731 results in 84 by pure arithmetic, and is not the correct number of days between August 15 and July 31. Similarly, 20040815 + 30 = 20040845, which is not a date at all. So, we have subroutines for dealing with these date operations. CHGDAT can be used to change the order of the components, from A6YMD to A8MDYY, for instance, so we can sort in YMD order, but display in MDY sequence. AYMD adds (or subtracts) some number of days from a traditional day, and similarly if no day is used, AYM will add or subtract months from a field with only Year and Month. YMD subtracts two dates in YMD sequence, MDY two dates in MDY order, and so forth. Of course, these subroutines must do the translation ("Thirty days hath September, April, June, and November…") under the covers. Along came "smart dates" that are not specified with a format type, but only the components Y, M, and optionally D. Now, instead of storing the digits, FOCUS stores the displacement from some base date. In the case of FOCUS files, this is 12/31/1900. Other data systems have different base dates. You no longer have to use a subroutine to subtract two dates, or add some number of days to that date, or even change the display. In fact, simply by assignment, FOCUS can also convert those old "traditional" dates (assuming they have valid date values) into smart dates. So, although the EMPLOYEE file contains HIRE_DATE described as A6YMD, we can: DEFINE FILE EMPLOYEE HDT1/MDY DFC 19 YRT 50 = HIRE_DATE; HDT2/YMD = HDT1 + 30; DIFF/I6 = HDT2 ­ HDT1; END This makes coding a lot simpler, without even calling subroutines. Just to be sure that we are all on the same page, remember that DFC defines the default century, and YRT the year threshold to determine the correct century. These can also be set globally through SET commands or in the MASTER File Description on either the field or file level. So, what about the subroutines, why do I need them? Well, once adding days became simpler, we now had to worry about the question, "How can I add months?" After all, what is a month after January 31? February 31? I don't think so! Human beings recognize that it is the last day of February, but computers are not that smart. Also, we might want to know how many working days are between two dates, not just the raw number of days. I don't want to be charged if there is there is a bank holiday. So, the subroutines have gotten a lot smarter when dealing with "smart dates" We can add or subtract not just days, but in units of YEAR, MONTH, DAY, BUSINESS DAY, WORK DAY, where SET BUSDAYS controls the business days (default is Monday thru Friday), and SET HDAY controls what holidays are to be taken into account. Then, DATEADD(date, 'unit', #units[, outfield]) will add some number of units to the original date giving the result, DATEDIF(from_date, to_date, 'unit'[, outfield]) will subtract two dates and return the resulting number of units. There is even DATEMOV, which will move a given date to another significant point, such as the beginning or end of the month. That brings me to one of the "gotchas." Look at the following request: DEFINE FILE SAMPLE DT2/WMDYY = DATEADD(DT1,'BD',1); END TABLEF FILE SAMPLE PRINT DT1 DT2 END DT1 DT2 --- --- WED, 08/24/2005 THU, 08/25/2005 THU, 08/25/2005 FRI, 08/26/2005 FRI, 08/26/2005 MON, 08/29/2005 SAT, 08/27/2005 TUE, 08/30/2005 SUN, 08/28/2005 TUE, 08/30/2005 Wait a minute here…the next business day after Saturday is Tuesday? When using the date subroutines, and BD or WD, the first determination is: Is the starting date a Work Day or Business Day? If not, FIRST move it to the next valid day, and then do the calculation. So, Saturday, 80/27/2005 is moved to Monday, 08/29/2005, and then we add 1 business day, giving 08/30/2005. In some cases this is valid &3150; for instance if it takes a business day to pack, and then the product will arrive on the following business day. I can't pack if I'm not in the office, so Monday is for packing, and Tuesday is shipping. Well, what if you don't want that: First, if it isn't a business day, move back to the last business day, and then add 1. Now, I know that people will look at the day of the week, but here's an easier way, use DATEMOV: DEFINE FILE SAMPLE DT1A/WMDYY=DATEMOV(DT1, 'BD-'); DT2/WMDYY = DATEADD(DT1A,'BD',1); END TABLEF FILE SAMPLE PRINT DT1 DT1A DT2 END DT1 DT1A DT2 --- ---- --- WED, 08/24/2005 WED, 08/24/2005 THU, 08/25/2005 THU, 08/25/2005 THU, 08/25/2005 FRI, 08/26/2005 FRI, 08/26/2005 FRI, 08/26/2005 MON, 08/29/2005 SAT, 08/27/2005 FRI, 08/26/2005 MON, 08/29/2005 SUN, 08/28/2005 FRI, 08/26/2005 MON, 08/29/2005 Notice that Wednesday through Friday, the date remained the same, as they were already business days. However, Saturday and Sunday moved back to Friday, so that the NEXT business day was correct. Another gotcha occurs while using BD (Business Day), which uses the HOLIDAY file (if SET HDAYS has been performed). If there are no dates in the HOLIDAY file greater than or equal to the year of the dates in the DEFINE, it is assumed that the HOLIDAY file is invalid, and only a 0 is returned. If you are subtracting two dates in 2005, and the latest date in the HOLIDAY file is 20041231, the subtraction will not be performed. One quick fix is to put a date very far in the future in any HOLIDAY file you create, and then it will always be considered valid (i.e., 29991231). Typically there is no need to use a subroutine to convert from traditional dates to smart dates, change the formats of smart dates, etc. DEFINE FILE SAMPLE TRAD_DT/A8YYMD = ‘20051231'; SM_DATE/MDYY = TRAD_DT; SM_DATE2/YYQ = SM_DATE; Etc. The one variation on this is with Dialogue Manager. Dialogue Manager does not differentiate between smart and traditional dates. They are always traditional dates. So, the DATEADD, DATEDIFF subroutines will not work, as they are developed for smart dates. Not to worry, use DATECVT. It's a little convoluted, but worth it if you need to use something other than day as the component. So, going back to one of the earlier examples, What is a month from January 31? -SET &STRT=DATECVT(20050131,'I8YYMD', 'YYMD'); -SET &NMT=DATEADD(&STRT,'M',1); -SET &NMTA=DATECVT(&NMT,'YYMD','A8MTDYY'); -TYPE A MONTH FROM 20050131 IS &NMTA A month from 20050131 is 02282005. Of course, these subroutines are all documented in the User's Manual, or you can do what I do: use the CD, and cut and paste them when you need to. Have fun, and remember those holidays.