There is frequently a need to extract basic date components (year, month, day) from a date-type field. A typical example would be to aggregate (sum, average, count) records over years or months. SQL provides generic functions to extract the basic date component from a date fields.

EXAMPLE DATABASE: World

DATA INPUT

Table politics in the database World.mdb is used as input.

QUERY split_date

SELECT country, independence, 
       YEAR(independence) AS ind_year, 
       MONTH(independence) AS ind_month, 
       DAY(independence) AS ind_day, 
       government
FROM politics
WHERE independence IS NOT NULL;

Query split_date produces the output below which show the complete date and the extracted basic date components as separate fields.

QUERY count_by_year

SELECT YEAR(independence) AS ind_year, COUNT(*) AS cnt
FROM politics
WHERE independence IS NOT NULL AND
      YEAR(independence) >= 1950 AND
      YEAR(independence) < 2000
GROUP BY YEAR(independence)
ORDER BY YEAR(independence);

Query count_by_year produces the output below which shows the number of countries per year gaining independence during the second half of the 20th century.

DISCUSSION

The queries above show how the basic date elements (year, month, day) can be extracted from a date field by using SQL functions. The extracted basic date elements can then be used like ordinary fields for joining tables, aggregating records, etc.

Note that the special operators IS and IS NOT can be used to determine whether a date field contains Null values or not.