Tech Tip — Uplink Archive

This tech tip explains how to use database functions in Access and FoxPro to determine the day of the week from date fields, allowing journalists to analyze patterns like which weekdays have the most arrests or traffic accidents without manually checking calendars.

Tech Tip What day is it?

By Richard Mullins NICAR/Missouri School of Journalism

If you wanted to find out what day of the week you were born, you could find a calendar for that year and look it up.

But if you wanted to find out which weekday had the most arrests or traffic accidents, the calendar lookup strategy would be too time-consuming for 50 records, let alone 150,000.

Access and FoxPro have functions that look at a date and determine the day of the week for that date. I'll explain both and show examples, starting with the FoxPro functions. For illustration, I'll assume a table called Arrests, with date-type field called BookDate.

You can see how this works with a query like this:

SELECT bookdate, dow (bookdate) ; FROM arrests

The dow() function returns the day-of-the-week as number, with Sunday being 1. If you want the name of the weekday, use cdow(). Here's how to remember the function names: DOW stands for DayOfWeek and CDOW stands for CharacterDayOfWeek.

SELECT bookdate, dow (bookdate), cdow (bookdate) ; FROM arrests

Since you can always retrieve this day-of-week information with the function, you don't need to store the weekday in a new field in order to use it for a query. To find the arrest counts by weekday, in descending order, you would write this group-by query:

SELECT cdow (bookdate) count (bookdate) FROM arrests ; GROUP BY 1 ; ORDER BY 2 DESC

In Access, you can get this same information with the format() function. The help file on this function is quite long, since the function is used for more than just dates.

Here is a general tip for help files: If you think that the help file on a particular topic is too confusing or too long, then look through the examples for the stuff that seems to fit what you want to do. Paste the example into your query and try it out.

Here is the general syntax for the Access format function as it applies to dates. You specify two parameters, or instructions. These go inside the parentheses after the function name and are separated by commas.

The first parameter is some date expression, like a literal date or a column containing a date. The second is format specifier, which is a quoted string of characters specifying how you want the day of the week to be expressed. Here is the syntax:

Format DateExpression, FormatTemplate

You have three choices for FormatTemplate:

W day of week as a number ddd as abbreviation (Sun-Sat) dddd as full name (Sunday-Saturday)

Applying this to the example table, here are the queries in Access:

To get the day of the week as a number:

SELECT bookdate, format (bookdate, "w") FROM arrests ;

To get the day of the week as an abbreviation:

SELECT bookdate, format (bookdate, "ddd") FROM arrests ;

To get the day of the week as a full name:

SELECT bookdate, format (bookdate, "dddd") FROM arrests ;

The Access query to show arrests by weekday, in descending order of the arrest count:

SELECT format (bookdate, "dddd"), count (bookdate) FROM arrests GROUP BY format (bookdate, "dddd"), ORDER BY count (bookdate) DESC ;

Can't turn your text file into a table? This question and more were answered in Tech Tip columns in 1996. The bound edition of 1996 Uplinks is now available for $20 plus shipping. To order, call Wendy Charron at (573) 882-0684.

Richard Mullins can be reached at (573) 882-2127, or send e-mail to richard@nicar.org