Although standard SQL database systems define the day to begin at 12:00:00 AM (midnight) and end at 12:59:59 PM, FileMan defines the day to begin at 00:01:01, military time, and to end at 24:00:00. Therefore, FileMan midnight today is midnight tomorrow to other databases. This leads to apparent inconsistencies in reports of the same data for the same range of dates produced by SQL Servers versus those produced by FileMan. The Department of Veterans Affairs is taking steps to assure correct projection of FileMan date/time fields to SQL, using the SQLI Interface (SQLI). Analysis shows that most of the inconsistencies can be resolved and that the practical impact is minimal.

FileMan Date/Time data type comes in three flavors: date only (D), date with optional time (DT) and date with required time (DTR). For DTR fields, the user must enter the time, and the time can’t be zero. If it were, the internal format yyymmdd.0 would convert to canonical form, just yyymmdd, and the information that a time had been entered would be lost. FileMan evades this issue by defining 1 second after midnight as the beginning of the day, and 86400 seconds after midnight as the end of the day, so all time values are non-zero. Internally, the time is stored as .000001 (1 second) through .24 (86400 seconds). The FileMan date/time conversion routines, %DT and %DTC, are consistent in maintaining this correspondence:

NOW	2970919.082701	SEP 19, 1997@08:27:01
T@MID	2970919.24	SEP 19, 1997@24:00:00
T@12A	2970919.24	SEP 19, 1997@24:00:00
T@12:00:01A	2970919.000001	SEP 19, 1997@00:00:01
T@12P	2970919.12	SEP 19, 1997@12:00:00

In M, of course, the day begins at 0 seconds since midnight and ends at 86399. Most database servers treat midnight as the beginning of a new day, as do Microsoft Open DataBase Connect (ODBC) and KB_SQL. ODBC and SQL times go from 00:00 through 23:59:

	September 19	September 20
ODBC/SQL	00:00:00..23:59:59
FileMan	00:00:01..23:59:59	24:00:00

Routine H^%DTC converts an internal FileMan date in variable X to an M Julian date in %H and seconds since midnight in %T. The internal time representation of midnight, .24, is converted to 86400 seconds. Because the time portion of the M $H standard date is constrained to 0 to 86399 seconds, the special case of midnight must be handled by adding 1 to %H and setting %T to 0.

Conversely, routine YMD^%DTC converts a $H date/time in %H to an internal FileMan date in X, and internal time in variable %. Given a $H time of zero, YMD returns %=0 and X containing the correct date; but YMD accepts 86400 as $H time, returning the same date, and %=.24. Note that 0 is an invalid FileMan internal time, so a $H time of zero can produce a correct type D or DT FileMan date, but not type DTR. Therefore, conversion of a $H time of zero to a DTR FileMan date requires subtracting one from the $H date and setting the $H time to 86400.

Routines that use the entry-points above must defend against producing invalid $H syntax. Corrections have been applied to the special FileMan SQL Interface (SQLI) date domains FM_MOMENT for DT date/time fields, and FM_DATE_TIME for DTR fields. KB_SQL mapping routines have also been fixed.

The FileMan day begins and ends exactly one second after the SQL day. This tiny difference means that midnight of 9/19/97 in a FileMan report corresponds exactly to midnight of 9/20/97 in most other database management systems, including KB_SQL. All times other than midnight have identical meanings in FileMan and other DBMS.

Any FileMan report constrained by a date or a range of dates that contains transactions time-stamped at midnight of the final date in the range will include those transactions. But an SQL report with the same constraints will include transaction time-stamped at midnight of the first date and exclude those of the last date.

The constraint "WHERE TRANSACTION_DATE = TODAY" must be replaced by the constraint "WHERE TRANSACTION_DATE BETWEEN TODAY@12:00:01AM AND TODAY+1@12:00:00AM" to reproduce the FileMan report in SQL. This will work only if TRANSACTION_DATE is a type DTR date (See Problems with Equality).

Date/time fields of type DT may be entered with or without time. When entered with time, they are always stored as decimal numbers; without time they are stored as 7-digit integers. Unfortunately, although the internal form can easily be converted to base ($H) format, the original internal form cannot be deduced from the base form for the special case of midnight dates.

KB_SQL converts external date parameters in a query to base format ($H) and then to internal format before using them for searching in indices or comparison. For DTR fields it’s known that the internal format for midnight must have FileMan’s .24 time value. For DT fields, there are two possible representations of the FileMan time of midnight for, say, Jan 1, 1997: 2970102, or 2970101.24. In KB_SQL the special case of midnight in a DT date/time field, the equality constraint is indeterminate. Both internal values convert to exactly the same value for $H, and KB_SQL can’t resolve the meaning of the query parameters to choose both internal FileMan values. In practice, 2970102 will always be chosen and 2970101.24 will be overlooked.

The meaning of midnight in FileMan is largely a notational problem. To say that a FileMan report produces a different list of rows than an SQL report with the "same" constraints doesn’t mean that either report is wrong. In fact, each produces correct results in the context of the report writer.

Midnight events are mercifully rare in FileMan databases, and with the exception of DT (time optional) date/time fields, there is a one-to-one correspondence of FileMan date/times with those of other DBMS. SQL reports constrained by D or DTR fields can be written to reproduce the results of FileMan reports; those constrained by DT fields cannot reproduce FileMan reports, but will produce correct results in the SQL context.

Updating type DT fields with SQL Data Modification Language (DML) is compromised by the indeterminacy of base to internal conversion. In the case where the form of entry (with or without date) must be preserved, the only solution is to bypass conversion entirely, and store the internal format explicitly.

Given an understanding of the issues discussed herein, the practical impact of the FileMan concept of midnight is minimal.