Wednesday, December 21, 2016

Easy way to convert date to words using SQL

convert date to an alphanumeric string

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.

17 comments:

  1. Gracias por compartir este post, esta muy interesante

    ReplyDelete
  2. FYI anyone, I believe DAY(CURRENT_DATE) does the same as DAYOFMONTH(CURRENT_DATE). Thanks Simon!

    Ringer

    ReplyDelete
  3. Is there a way to have the DAYNAME in other languages besides english?

    ReplyDelete
    Replies
    1. I am assuming that the words returned would depend on which language your IBM i is configured for. I have to say assume as all the ones I can use are set for American English.

      Delete
  4. Do you also have a slick algorithm for converting numbers to words?

    e.g.
    123,456,789 => "One hundred twenty-three million four hundred fifty-six thousand seven hundred eighty-nine"

    ReplyDelete
    Replies
    1. I have been looking for a feature in SQL to do this. Alas, I have not found one that does this. If I do it I can promise you it will be subject of a new post.

      Delete
  5. Straight SQL version:

    SELECT 'Today is ' CONCAT DAYNAME(CURRENT_DATE) CONCAT ', ' CONCAT MONTHNAME(CURRENT_DATE) CONCAT ' ' CONCAT DAYOFMONTH(CURRENT_DATE) CONCAT ', ' CONCAT YEAR(CURRENT_DATE) FROM sysibm.sysdummy1

    ReplyDelete
  6. I agree with you to the extent that it does simplify code and it does work as intended. However I have the problem of having clients in multiple languages and that makes me have to do quite a bit more.

    Is there any way to request those fields in other language, without messing with sysvals, by passing a parameter into that function and having SQL spit out the right value? Assume I already have the 'Today is' and all of that taken care of ;)

    Thanks for the post ;)

    ReplyDelete
    Replies
    1. I have changed the "language identifier" fron ENU to FRA, but, alas, name of the day returned is still in English.

      Create a file that has all of the days of the week in various languages. Now retrieve the day number, using the SQL listed above. Now CHAIN to the file with the language and the number then you have the name of the day in your desired language.

      Delete
  7. Why not create a UDF that does the translation into another language than US English ? As long as you do not pass parameters by value you can use an ILE procedure as a UDF. If you want I can put Simon's code into a procedure and transform it into a UDF that does the translation into Dutch, French or whatever.

    ReplyDelete
  8. Why not put the SQL code in a procedure that does the translation into another language and turn that into a UDF ? Then you have a way to use Simon's code in SQL with translation.

    ReplyDelete
  9. For current date I use VARCHAR_FORMAT.
    Please pay attention to abbreviation and case (upper/low)
    Select varchar_format(now(), ‘ yymmdd’) from sysibm/sysdummy1
    Select varchar_format(now(), ‘yyymmdd-hh24:mi:ss’) from sysibm/sysdummy1

    Select varchar_format(now(), ‘yyymmdd-hh24:mi:ss :nnnnnn’) from sysibm/sysdummy1 with microseconds

    Select varchar_format(now(), ‘ yyy/mm/dd’) from sysibm/sysdummy1
    Select dec(2||varchar_format(now(), ‘yymmdd’) ,7,0) sysibm/sysdummy1

    Select VARCHAR_FORMAT ((now()-1 days), ‘Mon’) from sysibm/sysdummy1

    Select VARCHAR_FORMAT ((now()-1 days), ‘DAY’) from sysibm/sysdummy1

    Select VARCHAR_FORMAT ((now()-2 month), ‘MONTH’) from sysibm/sysdummy1
    Select VARCHAR_FORMAT ((now()- 1 year), ‘Day’) from sysibm/sysdummy1

    Week of a year where week number starts from January 1 and ends on Januqary7
    Select VARCHAR_FORMAT (now(),’WW’) from sysibm/sysdummy1
    Current month week
    Select VARCHAR_FORMAT (now(),’W’) from sysibm/sysdummy1

    Julian date
    Select VARCHAR_FORMAT(now(),’yyyyddd’),8,0) as jdt from sysibm/sysdummy1

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.