Thursday, September 12, 2013

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:

 

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

No comments:

Post a Comment

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.