The Leading Educational Resource for IT Professionals

SQL 101: Date-Related Functions, Part 3 - Extracting Information from Dates

by Victoria Mack August 19, 2016 0 Comments

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’)



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”



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”



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’)



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’)



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-’)

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



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!


About the author: Rafael Victoria-Pereira

Victoria Mack
Victoria Mack


Also in MC Press Articles

Bluemix: A Viable Option for Power Customers

by Victoria Mack August 19, 2016 0 Comments

Just what is Bluemix, and what could it mean for you? An interview with an IBMer reveals the answers.

steve pitcherWritten by Steve Pitcher

Last week, I sat down with Adam Gunther, Director of Cloud Developers Services at IBM, to talk about IBM Bluemix. I told Adam I wasn’t a developer up front, but I wanted him to explain just exactly how my small-to-medium-sized business with an investment in on-premises infrastructure could really take advantage of Bluemix. I wasn’t disappointed.

Continue Reading →

Midrange MQ in an Open-Source World

by Victoria Mack August 19, 2016 0 Comments

MQ on IBM i continues to adapt to the needs of modern IT environments.

andrew schofieldWritten by Andrew Schofield

IBM MQ has been a familiar part of the corporate IT landscape for over 20 years. It’s been through a few name changes, but the fundamental idea of using asynchronous messaging to decouple communication between applications is as important now as it has ever been. Of course, over such a long period of time, there have been huge changes—in particular, the way that developers work using the Internet and open-source, and the rise of cloud computing. Therefore, we at IBM are doing many things in MQ to make sure that existing systems remain relevant and able to interact with the latest tools and platforms.

Continue Reading →

Using Scope in Linear-Main Programs to Create More Stable Applications

by Victoria Mack August 19, 2016 0 Comments

Linear-main RPG programs eliminate the RPG logic cycle and add new levels of variable scoping to protect your code from bugs down the road.

brian mayWritten by Brian May

While I am no expert in the RPG logic cycle, I have had to deal with it in older applications over the years. Most RPG developers have dealt with a logic cycle program at least once. I can honestly say I have never written a new logic cycle program, but I have seen others in the community doing it. This article is not intended to start a religious war about cycle programming. There are some who will never give it up. Instead, this article will demonstrate how to create a program without the logic cycle and concentrate on what I think is a very useful benefit to using linear-main procedures in program.

Continue Reading →