This article continues the date-related functions discussion, introducing a few more simple but extremely useful SQL functions: DAYOFWEEK, WEEK, QUARTER, DAYOFYEAR, and MIDNIGHT_SECONDS. Do you have time for some date fun?

Let me start with a quick flashback: an RPG Academy TechTip published in October 2015, explaining how to create an RPG function to calculate the day of the week of a given date stirred things up quite a bit. Some readers complained this kind of function was totally unnecessary, because SQL is better equipped to do this type of thing and so on. My reply was that I’d get to a point in the SQL 101 series in which I’d cover the “SQL version” of that particular function, named Clc_DayOfWeek.

Fast forward to today: SQL provides not one, but two functions (which do the exact same thing, as far as I know) that replace the aforementioned RPG function with a single statement. Functions DAYOFWEEK and DAYOFWEEK_ISO both accept a valid date representation in a date, timestamp, or string data type, and return an integer between 1 (meaning Sunday) and 7 (Saturday). As shown in another RPG Academy TechTip, the one that presented the Clc_HowLongUntilWE function, this can be useful to calculate the number of business days between two dates. Here’s an example:

SELECT      DAYOFWEEK(‘2015-03-04’)

FROM        SYSIBM.SYSDUMMY1

The execution of this statement will return 4, the numeric representation of Wednesday.

There are also two functions to retrieve the week number: WEEK and WEEK_ISO. Unlike the two day-related functions just presented, these work differently from each other. While WEEK considers that the week starts with Sunday and that January 1 is always in the first week, WEEK_ISO considers that the week starts with Monday and that week 1 is the first week of the year containing a Thursday, which is equivalent to the first week containing January 4. Thus, it is possible to have up to three days at the beginning of the year returned by WEEK_ISO as the last week of the previous year, or to have up to three days at the end of a year appear as the first week of the next year.

The input parameter is the same for both functions: the usual valid date representation in a date, timestamp, or string data type. The WEEK function returns an integer between 1 and 54, while WEEK_ISO returns the same data type from 1 to 53. Here’s an example, combining the two functions:

SELECT      WEEK(‘2015-01-01’) “WEEK”

            , WEEK_ISO(‘2015-01-01’) “WEEK_ISO”

FROM        SYSIBM.SYSDUMMY1

This will return two columns with the headings WEEK and WEEK_ISO, and a single line with two 1s. Why? Well, WEEK considers week 1 the week that contains January 1, while WEEK_ISO does the same for the first Thursday of the year. In 2015, January 1 was a Thursday, so both functions return the same thing. If I change the date to January 1, 1999, however, the results are different:

SELECT      WEEK(‘1999-01-01’) “WEEK”

            , WEEK_ISO(‘1999-01-01’) “WEEK_ISO”

FROM        SYSIBM.SYSDUMMY1

Here, the WEEK function still returns 1, but WEEK_ISO now returns 53, because it considers that January 1, 1999 is still part of the last week of 1998. Now that you’re aware of the possible discrepancies, choose between these functions carefully.

Determining the Quarter

If you need the quarter instead of the week, SQL’s QUARTER function is the answer. It shares the single parameter model of the previously presented functions and returns an integer between 1 and 4, representing the quarter in which the date represented in the input parameter resides. For example, any date in April, May, or June, regardless of the year, will cause the function to return a 2 (the second quarter). Here’s an example:

SELECT      QUARTER(‘2015-04-24’)

FROM        SYSIBM.SYSDUMMY1

This statement returns 2, because April is part of the second quarter.

I’m almost done with the date-extraction SQL functions. There are just two to go. The first is the DAYOFYEAR function, which has the customary input parameter and returns an integer representing how many days have elapsed since January 1 of the year represented in the input parameter. For instance, the following statement returns 34, because 34 days elapsed between January 1, 2015 and February 2, 2015:

SELECT      DAYOFYEAR(‘2015-02-02’)

FROM        SYSIBM.SYSDUMMY1

Similarly, the MIDNIGHT_SECONDS function returns the number of seconds that have passed since 00.00.00 of the date indicated in the input parameter. Note that a valid date representation, such as ‘2015-02-03’, implicitly contains 00.00.00 as its time, which will cause the function to return 0. With an input parameter that actually represents time, such as a time or timestamp, the output will be somewhere between 0 and 86.400. A couple of examples will make things clearer:

SELECT      MIDNIGHT_SECONDS(‘2015-02-03-00.01.00.12345’)

            , MIDNIGHT_SECONDS(‘2015-02-03-12.15.25.94834’)

FROM        SYSIBM.SYSDUMMY1

This returns 60 and 44.125. The first example should be simple to calculate; after all, only a minute (or 60 seconds) elapsed since midnight. The second example is a bit trickier, but the math is simple enough: (3600 * 12) + (60 * 15) + 25 = 44.125.

The next article will cover some more date math, discussing something that seems simple, but it’s not: adding and subtracting months to dates. Until then, I’d like you to share your real-world experience with these functions: how and why did you use them in a production environment? Use the comments section below to speak your mind!

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL