Effective date processing controls every entity in the database. Date processing is accomplished in the database so the application layer just sees valid current data unless it asks specifically for something else. The framework relies on the MSSQL date-time serial number to store dates. So when we talk about dates we are actually talking about date-time serial numbers accurate to within a second over thousands of years past and future.

There can be multiple versions of the same entity in the database at a single time. A version can be expired, effective or pending. The effective version of an entity at a point in time is considered "valid", or "valid for current use (VCU)." The scheme is implemented with two dates named "Effective" and "Expired" stored with each entity. The software compares these two dates to the target date, which is typically the system date-time (i.e. "Now"), to determine the date status of the entity:

• If target date is before the effective date the entity is pending, after the effective date the entity may be effective depending on the expiration date. If the effective date is null, it is considered to be less than the target.
  
• If the target date is before the expiration date the entity may be effective depending on the effective date. After the expiration date the entity is expired. If the expiration date is null, it is considered to be greater than the target.

Entity processing is typically conditioned by the entity's effective date status. Often this means the entity will only be presented to the application if it is valid for current use (ie current or effective). Pending and expired entries are not considered valid-for-current-use but they might be used in other processing. One, none, or multiple versions may be effective at any given time.

Typical transaction processing does not delete entries in the database. Instead a transactional delete simply sets the expiration to the target date-time. This provides several advantages for reconstruction of the system state at a given point in time be it past, present or future. Actual deletion of an entry from the database is an administrative function that may be requested to recover space. Deletion to a point in time allows administrators to delete just those entries which expired prior to a given point in time.

Often administrators have access to pending and expired entries for maintenance purposes.

Since there is heavy reliance on dates, the framework was built with a very effective means for specifying dates. Rather than simply using a text input box, a graphical display that looks like a miniature calendar is available to input dates. Use of the calendar eliminates the errors and questions that often arise in date entry by computer users. Do I need slashes between the month day and year? leading zeros?  four digit years? is the order of month day and year or something else? Is 23-Jan-06 a valid date?

Microsoft included a calendar control in the ASP.Net set of controls. It is used to select a date by clicking on a miniature calendar. It is a capable control but it runs on the server. So it requires roundtrips across the internet to transfer user inputs to the control. Rather than using the standard .net calendar we use a javascript calendar control with an attractive css display. This calendar runs locally on the client so it is fast and comprehensive including international formats.

In addition to the effective/expiration dates stored with each entity, the physical implementation of this design relies on database views and stored procedures.

It sounds complicated and under the covers it is fairly complex. However it is fast, convenient and very flexible. Because the progamming has already been done, you'll never have to worry about this stuff again.