Easy way to convert date to words using SQL

The idea for this post came from a meeting of the programming managers at work. My programming nemesis, the "I can do everything you do in RPGIV just as well using RPGIII" guy, was bragging that no-one could write a better way of changing a date into words than his old RPG38 program.

"I can do what your program does in just a few lines of code," I explained. He disputed that I could, and the challenge was on.

His old program is passed a date, as a number, and returns:

1. Day number of the week, where 1 = Sunday, 2 = Monday, etc.
2. Day of the week
3. Name of the month
4. The date in words, for example "THURSDAY JANUARY 1, 2017"

All of this information can be gathered using SQL functions. I have mentioned them in a previous post about date functions in SQL, and feel it is worth repeating.

I consider the code for this is so simple, I am going to jump straight into it with no further explanation.

Let me start with the section where I define all my variables.

01 **free 02 dcl-s DayName char(10) ; 03 dcl-s DayNumber zoned(1) ; 04 dcl-s DayOfMonth zoned(2) ; 05 dcl-s MonthName char(10) ; 06 dcl-s YearNumber zoned(4) ; 07 dcl-s FullDateString char(25) ; 08 dcl-s ThisDate date ; 09 dcl-s ThisChar char(10) ;

I am not going to explain much about this code as you regular readers of this blog will know what all this means. The only thing I will say is that the data type ZONED, lines 3, 4, and 6, is the equivalent of "signed" numeric in fixed format definitions.

Let me start with today's date.

10 exec sql SET :DayNumber = DAYOFWEEK(CURRENT DATE) ; 11 exec sql SET :DayName = DAYNAME(CURRENT DATE) ; 12 exec sql SET :DayOfMonth = DAYOFMONTH(CURRENT DATE) ; 13 exec sql SET :MonthName = MONTHNAME(CURRENT DATE) ; 14 exec sql SET :YearNumber = YEAR(CURRENT DATE) ; 15 FullDateString = %trimr(DayName) + ' ' + %trimr(MonthName) + ' ' + %char(DayOfMonth) + ', ' + %char(YearNumber) ; 16 dsply ('Today is ' + FullDateString) ;

By using CURRENT DATE in these statements it will always the date for today, whether I run this tomorrow, next week, next month, etc. I don't have to use a parameter with these functions. Unlike that program, where he has to pass UDATE to get the data for today.

Line 10: DAYOFWEEK returns a single numeric value to represent the day of the week. Sunday is 1, Monday 2, etc.

Line 11: DAYNAME returns the name of the day of the week, in mixed case. For example, "Sunday".

Line 12: DAYOFMONTH return the numeric value of the day of the month. For example if today is the 15th it returns 15.

Line 13: MONTHNAME returns the name of the month, in mixed case. For example, "December".

Line 14: YEAR returns the year from the date as a numeric value.

Line 15: Let me put all of that together. I am using this format as I am in the USA. You can easily change it to fit the format you are familiar with in your part of the world.

Line 16: Now let's display what I made. This gives me:

DSPLY Today is Wednesday December 21, 2016

And what about other dates, not just today.

17 ThisDate = d'1962-07-21' ; 18 exec sql SET :DayName = DAYNAME(:ThisDate) ; 19 exec sql SET :DayOfMonth = DAYOFMONTH(:ThisDate) ; 20 exec sql SET :MonthName = MONTHNAME(:ThisDate) ; 21 exec sql SET :YearNumber = YEAR(:ThisDate) ; 22 FullDateString = %trimr(DayName) + ' ' + %trimr(MonthName) + ' ' + %char(DayOfMonth) + ', ' + %char(YearNumber) ; 23 dsply ('07/21/1962 was ' + FullDateString) ;

Line 17: ThisDate is defined as a date field on line 8, and I am moving a date to it here.

Line 18 – 22: This is the same as I showed in the previous example, but rather than CURRENT DATE being the passed parameter I am using the date field.

When the DSPLY operation is performed I see:

DSPLY 07/21/1962 was Saturday July 21, 1962

I can even use character (alphanumeric) variables in these functions.

24 ThisChar = '04/27/1937' ; 25 exec sql SET :DayName = DAYNAME(:ThisChar) ; 26 exec sql SET :DayOfMonth = DAYOFMONTH(:ThisChar) ; 27 exec sql SET :MonthName = MONTHNAME(:ThisChar) ; 28 exec sql SET :YearNumber = YEAR(:ThisChar) ; 29 FullDateString = %trimr(DayName) + ' ' + %trimr(MonthName) + ' ' + %char(DayOfMonth) + ', ' + %char(YearNumber) ; 30 dsply ('04/27/1937 was ' + FullDateString) ;

These functions accept '04/27/1937' as it formatted like a valid date. When line 30 is executed I see.

DSPLY 04/27/1937 was Tuesday April 27, 1937

Providing the value in the character field is in a valid date format then these functions will accept it:

24 ThisChar = '1898-01-21' ; 30 dsply ('01/21/1898 was ' + FullDateString) ;

The above displays:

DSPLY 01/21/1898 was Friday January 21, 1898

Alas, when I try to use numbers as parameters the program will not compile.

So in six lines I can do the same as his 200+ line RPG38 program!

I call this individual my "programming nemesis" as a joke. He is a nice person, who I do socialize with outside of work. We just don't agree on the way to program on IBM i.

 

This article was written for IBM i 7.3, and should work for earlier releases too.