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.

5 comments:

  1. I have a question regarding the weeks:
    Q: I have a work date which I have to check "fiscal" table to find which fiscal period the date falls into; get the from and To dates of the pay period and calculate which week of that pay period my work date falls into.
    Any help on how to get that?
    Eg: Say my work date is 20170402 falls between 20170328 and 20170428 pay period in my "fiscal" table. I need to get the week number of 20170402 with 20170308 and 20170428 being the lower and upper limits respectively.

    Regards,
    Pavan

    ReplyDelete
    Replies
    1. Unless your fiscal year matches the calendar year you will not be able to use this.

      Delete
    2. Sadly, fiscal year is different from calendar year.

      Delete
  2. Hi Simon,

    i have a problem using week functions, because its using sunday as the start of week. what i need is monday as start of the week, using week_iso also incorrect.
    do you have any advice ?
    thanks.

    ReplyDelete
    Replies
    1. Both WEEK and WEEK_ISO are not incorrect they use the ANSI agreed rules, that a week starts on a Sunday.

      As your does not, as you have found, you cannot use these functions.

      The only other application I have seen were their week started on a Monday had a file that listed all of the dates for the year and the week number and quarter number for all of them.

      Delete

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.