Date fields can be compared or used in date arithmetic. In this example we compare two date-type fields from joined tables. Such date comparisons can be useful for joining tables, calculating new fields, checking data, etc.

EXAMPLE DATABASE: World

DATA INPUT

Tables country, politics and organization in the database World.mdb are used as input.

QUERY date_compare

SELECT country.name, 
       politics.independence, 
       organization.name, organization.established
FROM country, politics, organization 
WHERE country.code=politics.country AND
      country.code=organization.country AND
      politics.independence IS NOT NULL AND
      organization.established IS NOT NULL AND
      organization.established < politics.independence;

Query date_compare produces the output below which shows three records where international organizations were established in countries before they gained independence. In the organization table, the country code indicated the location of the headquarters of the organization. We assume that organizations did not move their headquarters after establishment.

DISCUSSION

The query joins three tables (country, politics, organization) by country code. The established date from the organization table is compared with the independence date from the politics table. Only those records are selected where the organizations establishment date is before or at the independence date of the host country. This can be considered a data quality query, as we would normally not expect such cases.