The functions discussed in this article won’t save time in the literal sense; mankind has yet to invent a time machine. However, these functions will surely cut down on the time it takes an RPG programmer to perform complicated “date math.”

As I mentioned in the previous article, there’s no BIF to return the day of the week, for instance. SQL provides several simple-to-use scalar functions that do that and more, as you’ll see later in this article.

Before getting to that, let me take a moment to address some readers’ remarks regarding the functions I presented in the previous article; there’s a common attribute to these three functions that some RPG programmers find strange: Unlike their RPG BIF counterparts, it’s not possible to use them without parameters. While in RPG you can assign the value of %DATE() to a variable and get the current date, DATE() won’t work in SQL. There’s another function for that: CURDATE. This function returns the current system date. It’s very simple to use:

SELECT      CURDATE()

FROM        SYSIBM.SYSDUMMY1

As you might have guessed, there’s also a function for the current system time: CURTIME. And yet another that returns the system time in a timestamp data type: NOW. Let’s see an example of these two functions:

SELECT      CURTIME()

            , NOW()

FROM        SYSIBM.SYSDUMMY1

It’s important to mention that whenever a statement has more than one of the CURDATE, CURTIME, or NOW functions, the value returned by the system is based on the same clock reading. In other words, even if the statement takes more than a second to execute, the values returned for the aforementioned functions will be similar, just presented with different precision.

SQL might be considered a bit quirky in this current date/time issue for an RPG programmer, but there are other date-related functions that every programmer will find most useful. For instance, RPG doesn’t provide a BIF to determine the day of the week. Let’s explore the alternatives SQL provides, starting with the DAYNAME function. This function takes a valid date representation, either in a date, timestamp, or string data type and returns the day of the week in the system’s national language. For instance, the code below returns ‘Wednesday’, assuming the system-configured language is English.

SELECT      DAYNAME(‘2015-03-04’)

FROM        SYSIBM.SYSDUMMY1

Yes, it’s really that simple. Equally simple is the function that returns the name of the month: MONTHNAME. This is also a quick win; you supply a valid date, just like you’d do in the DAYNAME function, and the MONTHNAME function returns the name of the month. Let’s see an example:

SELECT      MONTHNAME(‘2015-03-04’)

FROM        SYSIBM.SYSDUMMY1

This will return ‘March’, assuming that your system’s language is English. It’s really very simple and straightforward, right?

It’s also simple to extract parts of a date; in fixed-format RPG, there’s an operation code for that: EXTRCT. This evolved to the %SUBDT BIF, which is especially useful because EXTRCT didn’t make it to free-format RPG. Anyway, SQL has its own version of this operation code: the EXTRACT function. It works like its RPG counterpart but with increased readability. You basically “tell” the system to EXTRACT the YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND FROM a valid date, time, or timestamp representation, just like the previous functions. Noticed the weird capitalization? There’s a reason for that; just look at the example and you’ll figure it out:

SELECT      EXTRACT(DAY FROM ‘2015-03-04’)

FROM        SYSIBM.SYSDUMMY1

See what I mean? This returns 4 in integer format. Let’s see a time example next:

SELECT      EXTRACT(MINUTE FROM ’14.28.45’)

FROM        SYSIBM.SYSDUMMY1

This returns 28 in integer format.

Of course, there are also individual functions to extract each of the parts of a date, time, or timestamp; they have the same names as their RPG BIF counterparts. The YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, and MICROSECOND SQL functions receive (you guessed it) a valid date representation and return the namesake portion of the date/time/timestamp in integer format. Here’s an SQL statement that produces a nicely formatted date:

SELECT      DAYNAME(NOW())

           || ', '

           || MONTHNAME(NOW())

           || ' '

          || TRIM(CHAR(DAY(NOW())))

           || ', '

           || TRIM(CHAR(YEAR(NOW())))

FROM        SYSIBM.SYSDUMMY1

Depending on the date you’re reading this, the results will obviously vary. Assume that today is ‘2015-03-04’; the statement returns the following string: ‘Wednesday, March 4, 2015’. Notice that the MONTHNAME, DAY, and YEAR functions are enclosed within a CHAR function; this is necessary because these functions return an integer. I need to convert these functions’ output to a string in order to stitch them to form the output string. However, that output will contain a lot of blank spaces; the easiest way to remove them is by enclosing the CHAR functions in TRIM functions.

Just a side note: There’s also a DAYOFMONTH function that behaves exactly like the DAY function, accepting the same input and producing the same output. It would take a considerably longer piece of RPG code to produce this output, even with the help of ILE and BIFs.

The next article will continue to explore SQL’s “date math” functions, with a couple of them that can be particular useful in reports: WEEK, which returns the number of the week of the year for a given date, and QUARTER, which returns the quarter (1^st, 2^nd, 3^rd or 4^th) of a date specified by the user. Until then, I’m looking forward to your suggestions, comments, and questions! 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