Andrew Schulman 12 Humboldt Street Cambridge MA 02140 (617) 876-2102 1 December 1986 Power User PC MAGAZINE One Park Avenue New York NY 10016 DATE() + TIME() IN DBASE III (AND LOTUS DATE CONVERSION) ---------------------------- --------------------------- In dBase III, whereas the date() function returns a date value, time() returns a character string. An expression such as date() + time() produces a "Data type mismatch" error message from dBase, not a date with a fractional part representing the time of day. Since date variables are really just numbers (the number of days since 1 January 4713 BC -- not 1 January 1 AD, as is often stated), date arithmetic is not limited to whole numbers. For instance, today_noon = date() + .5: dBase accepts this "timely" date variable, and will use it in calculations (such as today_6pm = today_noon + .25). I wrote MAKETIME.PRG to help create date/time variables: . now = "" . do maketime with date(),time(),now . noon = "" . do maketime with date(),"12:00",noon dBase won't display the times we packed into date variables with MAKETIME: . list memory NOW pub D 12/01/86 NOON pub D 12/01/86 2 variables defined, 18 bytes used 254 variables available, 5982 bytes available but READTIME.PRG does (READTIME has lots of comments and runs faster if you remove them or use the DBC psuedo-compiler): . do readtime with now 12/01/86 7:57am . do readtime with noon 12/01/86 12:00pm READTIME can also be used to check non-variable values: . do readtime with date() + .75 11/29/86 6:00pm . do readtime with date() + .9999999 11/29/86 'round midnight (which also reveals readtime's grey area: 11:59pm-12:01 am). One hitch is that dBase doesn't store dates in database files in the same way that it holds them in memory. dBase .DBF files are almost text files. Whereas dates and numbers in dBase memory and in dBase .MEM files are represented as 8-byte IEEE floating-point numbers, they're just text in a .DBF file. For example, whereas a date variable with the date 1 December 1986 is represented in dBase memory as the Julian Period number 2446766, in a .DBF file it's a character string "19861201" (year then month then day, to facilitate indexing). What this means is that dates with times attached by MAKETIME would have their times stripped off before being stored in a .DBF file. Therefore, you need a NUMERIC field (width 17, 8 decimal places) to hold date/times. The MAKETIME program will create either a date variable or -- if you pass it a numeric variable as parameter -- a numeric variable: . use timetest . append blank . now = 0 . do maketime with date(),time(),now . replace date_time with now When it comes time to read: . use timetest . do readtime with date_time 11/29/86 11:34pm MAKETIME must be passed a pre-existing variable. Any variable other than numeric will be turned into a date variable with time attached. A numeric variable will stay a numeric variable and on return will hold the Julian Period number for the date and time specified. READTIME is also ambidextrous, handling either date or numeric input (uses dBase III type() function to figure out what type of parameter it's been passed). So you can use READTIME as a Julian period calculator: . do readtime with 2415386 01/01/1901 . do readtime with 3333333.3333333 04/03/4414 7:60am A useful feature of READTIME is its ability to decode the date numbers in .DBF files created by the Lotus Translate utilities (either 1-2-3 or Symphony). The Translate utilities turn Lotus dates into a dBase numeric field with numbers from 1 (1 Jan 1900) to 73050 (31 Dec 2099). For instance, in Lotus 1-2-3, DATES.WKS looked like this: A B 1 DATES COMMENTS 2 24-Nov-86 @date(86,11,24): 24-Nov-86 3 01-Jan-86 @date(86,1,1): 01-Jan-86 and after translation, DATES.DBF looked like this: . use dates . list off DATES COMMENTS 31740 @date(86,11,24): 24-Nov-86 31413 @date(86,1,1): 01-Jan-86 READTIME turns those machine-readable numeric dates back into something familiar looking: . use dates . do readtime with dates 11/24/86 . skip . do readtime with dates 01/01/86 READTIME also releases two PUBLIC variables, ddate and time_str, so that a dBase III program can use readtime.prg somewhat like a readtime() function (I realize that READTIME is not an appropriate name in the case of Lotus decoding): do while .not. eof() do readtime with dates if ddate < date() - 30 ? "Overdue" endif skip enddo Any Lotus dates prior to 1 March 1900 or following 28 February 2000 are off by one, by the way. This is due to an error in Lotus 1-2-3 and Symphony (the versions I've seen anyway) in which 1900 is a leap year (it wasn't) and 2000 is not a leap year (it will be)! This is not so much a bug as a fundamental misunderstanding of the Gregorian calendar. Sorry if this has run too long. I've enclosed a disk as well as hard copy. Also enclosed on the disk is MAKEMEM.ARC, a set of dBase III utilities written in C that address some of these issues. These are shareware programs; they are not required for running of MAKETIME/READTIME -- just thought you might be interested. Thanks,