Easy way to convert date to words using SQL.
This article was written for IBM i 7.3, and should work for earlier releases too. We just don't agree on the way to program on IBM i. He is a nice person, who I do socialize with outside of work. I call this individual my "programming nemesis " as a joke. So in six lines I can do the same as his 200+ line RPG38 program! Providing the value in the character field is in a valid date format then these functions will accept it:Īlas, when I try to use numbers as parameters the program will not compile. These functions accept ' ' as it formatted like a valid date. I can even use character (alphanumeric) variables in these functions.Ģ5 exec sql SET :DayName = DAYNAME(:ThisChar) Ģ6 exec sql SET :DayOfMonth = DAYOFMONTH(:ThisChar) Ģ7 exec sql SET :MonthName = MONTHNAME(:ThisChar) Ģ8 exec sql SET :YearNumber = YEAR(:ThisChar) Ģ9 FullDateString = %trimr(DayName) + ' ' + When the DSPLY operation is performed I see: 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. Line 17: ThisDate is defined as a date field on line 8, and I am moving a date to it here. This gives me:ĭSPLY Today is Wednesday December 21, 2016Īnd what about other dates, not just today.ġ8 exec sql SET :DayName = DAYNAME(:ThisDate) ġ9 exec sql SET :DayOfMonth = DAYOFMONTH(:ThisDate) Ģ0 exec sql SET :MonthName = MONTHNAME(:ThisDate) Ģ1 exec sql SET :YearNumber = YEAR(:ThisDate) Ģ2 FullDateString = %trimr(DayName) + ' ' + You can easily change it to fit the format you are familiar with in your part of the world. I am using this format as I am in the USA. Line 15: Let me put all of that together. Line 14: YEAR returns the year from the date as a numeric value. Line 13: MONTHNAME returns the name of the month, in mixed case. For example if today is the 15th it returns 15. Line 12: DAYOFMONTH return the numeric value of the day of the month. Line 11: DAYNAME returns the name of the day of the week, in mixed case. Line 10: DAYOFWEEK returns a single numeric value to represent the day of the week.
Unlike that program, where he has to pass UDATE to get the data for today. I don't have to use a parameter with these functions. 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.ġ0 exec sql SET :DayNumber = DAYOFWEEK(CURRENT DATE) ġ1 exec sql SET :DayName = DAYNAME(CURRENT DATE) ġ2 exec sql SET :DayOfMonth = DAYOFMONTH(CURRENT DATE) ġ3 exec sql SET :MonthName = MONTHNAME(CURRENT DATE) ġ4 exec sql SET :YearNumber = YEAR(CURRENT DATE) ġ5 FullDateString = %trimr(DayName) + ' ' +ġ6 dsply ('Today is ' + FullDateString) īy using CURRENT DATE in these statements it will always the date for today, whether I run this tomorrow, next week, next month, etc.
#Rpgle edit word code
I am not going to explain much about this code as you regular readers of this blog will know what all this means. Let me start with the section where I define all my variables.
I consider the code for this is so simple, I am going to jump straight into it with no further explanation. I have mentioned them in a previous post about date functions in SQL, and feel it is worth repeating. The date in words, for example "THURSDAY JANUARY 1, 2017"Īll of this information can be gathered using SQL functions.Day number of the week, where 1 = Sunday, 2 = Monday, etc.His old program is passed a date, as a number, and returns: He disputed that I could, and the challenge was on. "I can do what your program does in just a few lines of code," I explained. 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. The idea for this post came from a meeting of the programming managers at work.
0 kommentar(er)
