Problem solve Get help with specific problems with your technologies, process and projects.

Determine the last non-weekend date of a month with SQL

Learn how to determine the last non-weekend day of a given month in an SQL statement.

Ever need to determine the last non-weekend day of a given month in an SQL statement, without having to look at a calendar or look it up in a table, and then hard-code it in your statement?

Scenario: Marketing has asked for a quick calculation of the sales dollars invoiced for the last day of a given month.

All you have to do is use the first day of the requested month, which will always be 1, add one month to it, then subtract one day. Then check the day of the week using a CASE statement. If the DAYOFWEEK = 1, this is Sunday, back up two more days (total of three days). If the DAYOFWEEK = 7, this is Saturday, back up one more day (total of two days). Otherwise, just back up a total of one day. If you CAST your field as either a DATE or TIMESTAMP then you can add and/or subtract durations of months and/or days, and determine the day of week.


 
SELECT  INVDATE, SUM(SELLPRICE*SHIPQTY)  FROM LIBRARY/FILENAME               
GROUP BY INVDATE                                                         
HAVING INVDATE =                                                         
CASE WHEN DAYOFWEEK(CAST('2002-03-01' AS DATE) + 1 MONTH - 1 DAY) = 1  
          THEN CAST('2002-03-01' AS DATE) + 1 MONTH - 3 DAY            
     WHEN DAYOFWEEK(CAST('2002-03-01' AS DATE) + 1 MONTH - 1 DAY) = 7  
          THEN CAST('2002-03-01' AS DATE) + 1 MONTH - 2 DAY  ELSE      
               CAST('2002-03-01' AS DATE) + 1 MONTH - 1 DAY  END   

This will also work with the date formatted as '03/01/2003'.

Or, if you want to set this up to run for the previous month, whatever that month might be, use CURRENT_DATE, changing the day portion to 01, then subtract 1 day:

 
SELECT  INVDATE, SUM(SELLPRICE*SHIPQTY)  FROM LIBRARY/FILENAME              
GROUP BY INVDATE                                                        
HAVING INVDATE =                                                        
CASE WHEN DAYOFWEEK(CAST(SUBSTR(CHAR(CURRENT_DATE),1,3) || '01' ||    
SUBSTR(CHAR(CURRENT_DATE),6,5)  AS DATE) - 1 DAY) = 1                 
  THEN              CAST(SUBSTR(CHAR(CURRENT_DATE),1,3) || '01' ||    
SUBSTR(CHAR(CURRENT_DATE),6,5)  AS DATE) - 3 DAY                      
     WHEN DAYOFWEEK(CAST(SUBSTR(CHAR(CURRENT_DATE),1,3) || '01' ||    
SUBSTR(CHAR(CURRENT_DATE),6,5)  AS DATE) - 1 DAY) = 7                 
  THEN              CAST(SUBSTR(CHAR(CURRENT_DATE),1,3) || '01' ||    
SUBSTR(CHAR(CURRENT_DATE),6,5)  AS DATE) - 2 DAY                      
  ELSE              CAST(SUBSTR(CHAR(CURRENT_DATE),1,3) || '01' ||    
SUBSTR(CHAR(CURRENT_DATE),6,5)  AS DATE) - 1 DAY  END       

DATE fields are in the format of 'YYYY-MM-DD'. TIMESTAMP fields are in the format of 'YYYY-MM-DD-HH.MM.SS.000000'. Note: These formats may vary slightly depending on your system. The easiest way to determine your format is to do a simple SQL like the following, then adjust the above SQLs accordingly:

SELECT INVDATE, CAST(INVDATE AS DATE), CAST(INVDATE AS TIMESTAMP) FROM LIBRARY/FILENAME

Invoice Date CAST function CAST function
------------ ------------- --------------------------
01/02/2001 01/02/2001 2001-01-02-00.00.00.000000

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

Dig Deeper on iSeries CL programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close