More SQL and dates part I

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:

Day

/free wkDate = %date() ; exec sql set :Nbr = dayofweek(:wkDate) ; exec sql set :Nbr = dayofweek_iso(:wkDate) ;

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.

exec sql set :Alpha = dayname(:wkDate) ;

DAYNAME returns the name of the day of the week, today it would return 'Thursday'.

exec sql set :Nbr = dayofmonth(:wkDate) ;

DAYOFMONTH returns the day of the month, today it would return 12.

exec sql set :Nbr = dayofyear(:wkDate) ;

DAYOFYEAR returns the day of the year, today it would return 224.

exec sql set :NextDate = next_day(:wkDate, 'Mon') ;

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

exec sql set :Nbr = week(:wkDate) ; exec sql set :Nbr = week_iso(:wkDate) ;

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

exec sql set :Nbr = month(:wkDate) ;

MONTH returns the number of the month, today it would return 9.

exec sql set :Alpha = monthname(:wkDate) ;

MONTHNAME returns the name of the month, today it would return 'September'.

Year

exec sql set :Nbr = year(:wkDate) ;

YEAR returns the number of the year, today it would return 2013.

Date formatting

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.

Code	Format
ISO	YYYY-MM-DD
USA	MM/DD/YYYY
EUR	DD.MM.YYYY
JIS	YYYY-MM-DD
LOCAL	System Value dependent (see QDATFMT). As I am in USA it is MM/DD/YY.

exec sql set :Alpha = char(:wkDate, ISO) ; exec sql set :Alpha = char(:wkDate, USA) ; exec sql set :Alpha = char(:wkDate, EUR) ; exec sql set :Alpha = char(:wkDate, JIS) ; exec sql set :Alpha = char(:wkDate, LOCAL) ;

The CHAR can also be used for Time and Timestamp data type fields.

Conclusion

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:

exec sql set :Alpha = dayname('06/03/1992') ;

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:

• DAYOFWEEK
• DAYOFWEEK_ISO
• DAYNAME
• DAYOFMONTH
• DAYOFYEAR
• NEXT_DAY
• WEEK
• WEEK_ISO
• MONTH
• MONTHNAME
• YEAR
• CHAR

 

This article was written for IBM i 7.1, and it should work with earlier releases too.