Occasionally, there is a need to extract more complex date components (weekday, day-of-year) from a date field. In a survey or experimental dataset, a frequency count of weekdays can be useful to discover unusual pattern that may indicate data quality problems, e.g. surveys or experimental treatments done on Saturdays or Sundays. Turning a date field into a simple ordinal variable by converting date values into day-of-year values allows easier plotting of data.

EXAMPLE DATABASE: World

DATA INPUT

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

QUERY weekday

SELECT country, independence, WEEKDAY(independence) AS weekday, government
FROM politics
WHERE independence IS NOT NULL;

Query weekday produces the output below which shows an additional numeric column representing the weekday. The week begins with Sunday (1) and ends with Saturday (7).

QUERY day_of_year

SELECT country, independence, 
       independence - DATESERIAL(YEAR(independence), 1, 1) + 1 AS day_of_year,
       government
FROM politics
WHERE independence IS NOT NULL;

Query day_of_year produces the output below which shows an additional numeric column representing the day of the year. The year begins with January 1st (1) and ends with December 31st (365 in a normal year, 366 in a leap year).

DISCUSSION

The queries above show how the more complex date elements (weekday, day-of-year) can be extracted from a date-type field. The extracted date elements can then be used like ordinary fields for aggregating records, plotting data, etc.

While there is a special function for extracting the weekday from a date value, the day-of-year conversion is more complex.

The following expression converts a date into the corresponding day-of-year value:

day_of_year = date - DATESERIAL(YEAR(date), 1, 1) + 1

This expression subtracts from a given date the 1st of January of the same year, and adds 1 to the resulting difference.