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
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online