After my post on calculating the last day of the month using SQL, Calculating end of month part II, I received an email from Scott Damery.
"Take advantage of the many date functions in SQL instead of programming code to provide the information needed," he said in his email.
I am a great believer in: if there is a function to do a process use it, rather than code your own.
Below are the SQL functions he mentioned with a couple of others I found:
The DAYOFWEEK and DAYOFWEEK_ISO functions are basically the same, they return an number that represents the day of the week.
The difference is when the week starts. For DAYOFWEEK 1 is Sunday and 7 is Saturday. For DAYOFWEEK_ISO 1 is Monday and 7 is Sunday.
DAYNAME returns the name of the day of the week, today it would return 'Thursday'.
DAYOFMONTH returns the day of the month, today it would return 12.
DAYOFYEAR returns the day of the year, today it would return 224.
NEXT_DAY returns date of the next day requested. In this example I have requested the date of next Monday, which gives me the date '09/16/2013'.
Either the three letter abbreviation of the day or the day name can be used, and it is not case sensitive.
WEEK returns a number, between 1 and 54, that represents the week of the year. The week starts with Sunday. January 1 is always the start of the first week.
WEEK_ISO is different. It returns a number, between 1 and 53, that represents the week of the year. Week 1 is the first week of the year that contains a Thursday, therefore, there could be days at the beginning of the year that are included in the last week of the previous year, or days at the end of the year that are included in the first week of the next year. For example: 12/31/1997 is included in the first week of 1998, and 01/01/2000 is included in the 52nd week of 1999.
MONTH returns the number of the month, today it would return 9.
MONTHNAME returns the name of the month, today it would return 'September'.
YEAR returns the number of the year, today it would return 2013.
I can use SQL to output the date from a date data type field into an alphanumeric field using the CHAR function. The valid codes for the date formats are shown in the table below, and must be entered in upper case.
|LOCAL||System Value dependent (see QDATFMT).|
As I am in USA it is MM/DD/YY.
exec sql set :Alpha = char(:wkDate, ISO) ;
The CHAR can also be used for Time and Timestamp data type fields.
In the above examples I have used a field for the expression in all of the functions. You can also hard code in the expression, for example:
In part two I will discuss how to calculate the difference between two dates using SQL.
You can learn more about all of these SQL functions on IBM's website:
This article was written for OS i 7.1, and it should work with earlier releases too.
Read the original at RPGPGM.COM - From AS400 to IBM i.