Introduction
I became interested in month-name ordering when I made a database table in Paradox to store birthdays and anniversaries. Since I do not have all of the year information and wanted to display month names, I included columns for month name, day, year and month number. The last of these was included so I could sort by month number, day, last name and first name.
Since I only occasionally used Paradox, I did not find how to use SQL to create a display that is based on two tables. At the time, I thought it would be good if database products included a month-text column type that sorts month names in the proper order. Below, I will show various ways to accomplish the same thing without needing to include both a month-name column and a month-text column, and without database products needing to be modified. For testing purposes, I used an implementation of SQLite.
Another use for month-name ordering is sorting filenames. You can have files that are named after months and are updated in any order. As a result, you cannot rely on sorting by modification date/time. An example is a promotion plan for a bookstore where authors come in for book signings on dates after their books are released. Their appearances may need to be rescheduled in any order.
Reference 1 is my original article on this topic. I decided to update that article since I found and thought of better ways of accomplishing the same goals.
Reference 2 explains how to sort a column by month name in Excel 2007. Basically, you select custom sorting and then the custom list for month names. Reference 3 provides more information about custom lists in Excel.
Reference 4 describes which portion of SQL is incorporated into SQLite.
Database Table with Month Numbers
Consider the following database table:
CREATE TABLE BirthdaysMonthNums (
FirstName TEXT,
LastName TEXT,
MonthNum INTEGER,
Day INTEGER
);
If your database product supports DATENAME[5], then you can use the following query. In this query, the plus sign is the concatenation operator. In SQLite, the concatenation operator is ||.
SELECT FirstName, LastName, DATENAME (month, '2000-' + MonthNum + '-01'), _
Day FROM BirthdaysMonthNums ORDER BY MonthNum, Day, LastName, FirstName;
If your database product doesn’t support DATENAME, then you need a table that maps month numbers to month names.
CREATE TABLE MonthNames (
MonthNumber INTEGER,
MonthName TEXT
);
INSERT INTO MonthNames VALUES (1, 'January');
INSERT INTO MonthNames VALUES (2, 'February');
INSERT INTO MonthNames VALUES (3, 'March');
INSERT INTO MonthNames VALUES (4, 'April');
INSERT INTO MonthNames VALUES (5, 'May');
INSERT INTO MonthNames VALUES (6, 'June');
INSERT INTO MonthNames VALUES (7, 'July');
INSERT INTO MonthNames VALUES (8, 'August');
INSERT INTO MonthNames VALUES (9, 'September');
INSERT INTO MonthNames VALUES (10, 'October');
INSERT INTO MonthNames VALUES (11, 'November');
INSERT INTO MonthNames VALUES (12, 'December');
Then the query is:
SELECT FirstName, LastName, MonthName, Day FROM BirthdaysMonthNums, _
MonthNames WHERE MonthNum = MonthNumber ORDER BY MonthNumber, Day, _
LastName, FirstName;
Database Table with Month Names
Placing month names in a birthday/anniversary table makes it easier to read rows since many people prefer to think of dates as having month names. However, the downsides include using more space by repeating month names and the possibility of misspelling month names. Nevertheless, it is possible to sort a table that includes month names.
Consider the following database table:
CREATE TABLE BirthdaysMonthNames (
FirstName TEXT,
LastName TEXT,
Month TEXT,
Day INTEGER
);
If your database product supports DATEPART[6], then you can use the following query, which is based on reference 7.
SELECT * FROM BirthdaysMonthNames order by DATEPART _
(mm, CAST (Month + ' 1900' AS DATETIME)), Day, LastName, FirstName;
Alternatively, your could use this query, which is based on reference 8.
SELECT * FROM BirthdaysMonthNames ORDER BY MONTH ('1' + Month + '00'), _
Day, LastName, FirstName;
In this query, MONTH (date) has the same effect as DATEPART (month, date) [9] and the cast is implicit.
If your database product doesn’t support DATEPART or MONTH, then you need a table that maps month names to month numbers.
CREATE TABLE MonthOrder (
MonthName TEXT,
MonthNumber INTEGER
);
INSERT INTO MonthOrder VALUES ('January', 1);
INSERT INTO MonthOrder VALUES ('Jan', 1);
INSERT INTO MonthOrder VALUES ('February', 2);
INSERT INTO MonthOrder VALUES ('Feb', 2);
INSERT INTO MonthOrder VALUES ('March', 3);
INSERT INTO MonthOrder VALUES ('Mar', 3);
INSERT INTO MonthOrder VALUES ('April', 4);
INSERT INTO MonthOrder VALUES ('Apr', 4);
INSERT INTO MonthOrder VALUES ('May', 5);
INSERT INTO MonthOrder VALUES ('June', 6);
INSERT INTO MonthOrder VALUES ('Jun', 6);
INSERT INTO MonthOrder VALUES ('July', 7);
INSERT INTO MonthOrder VALUES ('Jul', 7);
INSERT INTO MonthOrder VALUES ('August', 8);
INSERT INTO MonthOrder VALUES ('Aug', 8);
INSERT INTO MonthOrder VALUES ('September', 9);
INSERT INTO MonthOrder VALUES ('Sept', 9);
INSERT INTO MonthOrder VALUES ('Sep', 9);
INSERT INTO MonthOrder VALUES ('October', 10);
INSERT INTO MonthOrder VALUES ('Oct', 10);
INSERT INTO MonthOrder VALUES ('November', 11);
INSERT INTO MonthOrder VALUES ('Nov', 11);
INSERT INTO MonthOrder VALUES ('December', 12);
INSERT INTO MonthOrder VALUES ('Dec', 12);
This table includes abbreviations but does not include periods after these abbreviations.
Then the query is:
SELECT FirstName, LastName, Month, Day FROM BirthdaysMonthNames, _
MonthOrder WHERE RTRIM (Month, '.') LIKE MonthName ORDER BY MonthNumber, _
Day, LastName, FirstName;
In this query, the comparison of months is case insensitive and periods are trimmed from the right of month strings. This allows some flexibility in how months are set in BirthdaysMonthNames.
Filename or String Sorting
In filename or string sorting, you need a way to identify month names. In English, March, April, May and June could be common words (march and may) or female names (April, May and June). One way to accomplish this is to surround month names with braces. In the MonthNameOrdering program, it is assumed that entire strings are month names. See the above figure.
To use this program, first load or add-in a set of month names. The available options include English and French month names as full names, abbreviations or both. These are based on the solar year. Also included are options for the Jewish calendar, which is a lunar calendar that may have leap years[10]. In contrast to solar leap years, a lunar leap year includes an additional month.
The Chinese calendar also has leap years. However, the selection of the month to repeat is irregular. It is inserted every 32 or 33 months [11] or about every three years and its name is the same as that of the previous month [12].
After loading one or more sets of month names, press either the Sort Alphabetically button or the Sort by Month Order button. In both cases, duplicate names will be removed.
To sort alphabetically, the month names are copied into an STL vector and then sorted by calling std::sort. Duplicates are then removed by calling std::unique followed by calling the vector’s erase member function if needed.
To sort by month order, std::sort is called with a lambda function as its third parameter. This function uses an STL map between month names and month numbers. It could simply be as follows:
[&](CString str1, CString str2)
{return m_mapMonthNames[str1] < m_mapMonthNames[str2];}
However, to allow for placing longer names for the same month first, this lambda function becomes as follows:
[&](CString str1, CString str2) {
if (m_mapMonthNames[str1] == m_mapMonthNames[str2])
return str2.GetLength() < str1.GetLength();
else
return m_mapMonthNames[str1] < m_mapMonthNames[str2];
}
m_mapMonthNames is defined when month names are loaded and allows for abbreviations to have the same month index as the corresponding full name. In addition, the function for setting this map allows for mixing of solar and lunar calendars. It accomplishes this by ORing the month number with a level number that is in the high part of an integer.
m_mapMonthNames[strTrimmed] = nMonth | (nLevel << 16);
Month names that have the same index as the previous one start with an equals sign, which is then trimmed from the names. You could store the result of (nLevel << 16) in a variable that is defined outside of the loop that sets m_mapMonthNames so that this calculation is not repeated in this loop.
References
- [1] Wincelberg, D., "Month-Text Ordering," Dr. Dobb's Journal, December 2005.
- [2] "Sorting Month Names in Calendar Order," Tech Support Guy, May 6, 2010.
- [3] "Create or delete a custom list for sorting and filling data," Microsoft Office Support.
- [4] "SQL As Understood By SQLite," SQLite.org.
- [5] "DATENAME (Transact-SQL)," Microsoft.
- [6] "DATEPART (Transact-SQL)," Microsoft.
- [7] Kommery, A., "How to use Order by MONTH name of data type CHAR/VARCHAR in SQL," May 14, 2013.
- [8] Imran, M., "SQL SERVER – How to sort month names in month order instead of alphabetical order – Part II," June 27, 2013.
- [9] "MONTH (Transact-SQL)," Microsoft.
- [10] "The Jewish Calendar," Time and Date.
- [11] Van Hinsbergh, G., "The Chinese Calendar," China Highlights, April 25, 2017.
- [12] "The Chinese Calendar," Time and Date.
