Calculating Days of the Week and Accounting Months 5-4-4

My last article related to date calculations, which was written a few years ago, has generated a number of questions from readers. More than one reader has asked me if I knew how to calculate a certain day of the week based on the current date. Therefore, I have decided to write another article that will include a number of new date calculations related to days of the week. In addition, one of my readers provided me with the code for a specific date calculation related to Account Months 5-4-4, so I’m going to share that with you as well. Prior to discussing my different date examples, we need to discuss some of the SQL Server date functions that are useful when working with date fields.

GETDATE Function

The GETDATE function in SQL Server accepts no parameters and returns the current date and time. The returned date/time value is in the internal format SQL Server uses when storing datetime values.

DATEDIFF Function

The DATEDIFF function returns an integer number that represents the number of time intervals that have passed between two different dates. The DATEDIFF function requires three parameters: datepart, startdate, and enddate. The “datepart” parameter can have the following values:

DATEADD Function

The DATEADD function returns a datetime value that is calculated by adding a number of date intervals to a specific date. The DATEADD function requires 3 parameters: datepart, number of intervals to add, and date. The “datepart” values are the same as the above DATEDIFF function.

Last Monday of the Month

To calculate the last Monday of the month I need the following two different date related values:

• Last known Monday (Last_Known_Monday)
• Number of Mondays between the last known Monday and the last day of the current month (#_of_Mondays)

If you know this information, you can use the following formula to calculate the last Monday of the month.

Last Monday of the Month =  Last_Know_Monday +  (#_of_Mondays) * 7

All this formula does is add the appropriate number of days to the last known Monday in order to come up with the last Monday of the current month. So now, let’s look at some T-SQL code that implements this formula.

SELECT DATEADD(DD,
               -- NUMBER OF DAYS SINCE LAST KNOWN MONDAY AND THE LAST MONDAY OF MONTH
               DATEDIFF(DD,
                       '2006-11-27',
                        -- LAST DAY OF CURRENT MONTH
                        DATEADD(MS,
                                -3, 
                                -- FIRST DAY OF NEXT MONTH     
                                DATEADD(MM, 
                                        -- NUMBER OF MONTH SINCE 2006-12-01 + 1
                                        DATEDIFF(M,
                                                 '2006-12-01',
                                                 GETDATE( ) 
                                                )+1,
                                        '2006-12-01'
                                       )
                               )    
                       ) / 7 * 7,
               '2006-11-27'
              )

Let me take apart this query one piece at a time. The first thing this query needs to determine is the last day of the month. In order to do this the following code is run:

-- LAST DAY OF CURRENT MONTH
DATEADD(MS,
        -3, 
        -- FIRST DAY OF NEXT MONTH     
        DATEADD(MM, 
                -- NUMBER OF MONTH SINCE 2006-12-01 + 1
                DATEDIFF(M,
                         '2006-12-01',
                         GETDATE( ) 
                        )+1,
                '2006-12-01'
               )
       )    

In this code, I start by calculating the first day of the next month. To do this it takes three different date calculations. This first calculation is the calculate the number of months boundaries that have crossed since some first day of the month. In my case, I choose December 1, 2006 for the day that represents the first day of the month. In reality, you could use any first day of the month. I use the DATEDIFF function with the “M” interval to calculate the number of month boundaries crossed since the identified first day of the month (12/1/2006) and the current date. I then use the number of month boundaries crossed to calculate the first day of the next month. This is done using the DATEADD function to add the number of month boundaries crossed + 1 to the known first day of the month. The last calculation is to subtract 3 milliseconds from the first day of the next month to determine the last day of the current month. I again use the DATEADD to do this calculation.

The last two date calculations are to determine the number of days since the last known Monday, and then add that number of days to the last known Monday. Below is how those two calculations are done:

DATEADD(DD,
        -- NUMBER OF DAYS SINCE LAST KNOWN MONDAY AND THE LAST MONDAY OF MONTH
        DATEDIFF(DD,
                 '2006-11-27',
                 <Last Day of Month code above goes here>
                ) / 7 * 7,
       '2006-11-27'
       ) 

In order to calculate the number of Mondays since the last known Monday I take advantage of SQL Servers ability to truncate the fractional portion of the division operation when an integer value is used. In the above calculation when I use DATEDIFF to calculate the number of days between the last day of the month and the last known Monday and then divide the result by 7, SQL Server will return an integer value that represents the number of Mondays since the last known Monday. Now when I multiple by 7, I get the number of days from the last known Monday to the last Monday of the month. With one final DATEADD function I add the number of days I just calculated to the last known Monday, and my result becomes the date for the Last Monday of the current month.

Calculating the Any Monday of the Month

In this example, I will show you how to calculate any Monday of the current month. Although keep in mind that some months have only 4 Mondays while other months have 5 Mondays. Therefore, if you try to use this example to calculate the 5^th Monday of the month it will only work if there are 5 Mondays in the month. If you use this formula to calculate the 5^th Monday of the month and there are only 4 Mondays in the month, than the code will calculate the first Monday of the next month. Here is my T-SQL code to calculate the second Monday of the month:

DECLARE @WHICHMONDAY INT
SET @WHICHMONDAY = 2
SELECT DATEADD(DD,
               -- NUMBER OF DAYS SINCE LAST KNOWN MONDAY AND THE @WHICHMONDAY OF MONTH
               DATEDIFF(DD,
                        '2006-11-27',
                        -- X NUMBER OF WEEKS FROM THE FIRST DAY OF THE MONTH
                        DATEADD(DD,
                                (@WHICHMONDAY * 7) -1,
                                -- FIRST DAY OF MONTH
                                DATEADD(MM, 
                                        DATEDIFF(MM,
                                                 '1/1/2007',
                                                 GETDATE()
                                                ), 
                                        '1/1/2007'
                                       )
                               )    
                       ) / 7 * 7,
               '2006-11-27'
              )

This code is similar to the example that calculated the last Monday of the month, so I will not explain the entire set of T-SQL code. As you can see, I’ve defined a variable @WHICHMONDAY. This integer variable is used to identify which Monday of the month I want to calculate. In the code above, I am calculating the second Monday of the month, so I set this variable to 2. By changing this variable, you can calculate any Monday of the month. To calculate the 4^th Monday of the month you would just set this variable to 4. The other thing to note about this code is how I determine how many weeks out for which I want to display Monday’s date. I do this by using the DATEADD function to add X number of days to the first day of the month, where X is @WHICHDAY time 7 minus 1.

With a slight modification to the code, the above can be used to calculate so many Mondays from the current date, like below:

DECLARE @WHICHMONDAY INT
SET @WHICHMONDAY = 2
SELECT DATEADD(DD,
               -- NUMBER OF DAYS SINCE LAST KNOWN MONDAY AND THE @WHICHMONDAY OF MONTH
               DATEDIFF(DD,
                        '2006-11-27',
                        -- X NUMBER OF WEEKS FROM THE FIRST DAY OF THE MONTH
                        DATEADD(DD,
                                (@WHICHMONDAY * 7) -1,
                                -- TODAY'S DATE
                                getdate()
                               )    
                       ) / 7 * 7,
               '2006-11-27'
              )

Here I have replaced the section of code that calculated the first day of the month with code that calculated the current date ( getdate()). So now, this code calculates two Mondays from today’s date.

Second Thursday of the Month

My code for calculating any Monday of the month can also be used to calculate any particular day of the week. Here is the code to calculate the second Thursday of the month:

DECLARE @WHICHTHURSDAY INT
SET @WHICHTHURSDAY = 2
SELECT DATEADD(DD,
               -- NUMBER OF DAYS SINCE LAST KNOWN THURSDAY AND THE @WHICHMONDAY OF MONTH
               DATEDIFF(DD,
                        '2006-11-30',
                        -- X NUMBER OF WEEKS FROM THE FIRST DAY OF THE MONTH
                        DATEADD(DD,
                                (@WHICHTHURSDAY * 7) -1,
                                -- FIRST DAY OF MONTH
                                DATEADD(MM, 
                                        DATEDIFF(MM,
                                                 '1/1/2007',
                                                 GETDATE()
                                                ), 
                                        '1/1/2007'
                                       )
                               )    
                       ) / 7 * 7,
               '2006-11-30'
              )

The only real change I made to the logic of my code, to make this code calculate the second Thursday of the month, was I change “2006-11-27” to “2006-11-30”. What this did was change the known date to a Thursday. So if you wanted to make this code work for a Tuesday, just change all the occurrences of “2006-11-30” to a known Tuesday date. So now, here is some code that can be easily tailored to calculate any day of any week in the month.

Accounting Months 5-4-4

In the accounting world, there is the need to break up the year into 4 distinct quarters of equal parts, where each part consists of 13 weeks. Those 13 weeks are then broken up into 3 different accounting months, where the first month consists of 5 weeks, and the next 2 months are each 4 weeks long. Therefore, there is the need to calculate the beginning and ending date for the accounting quarter, as well as the beginning and ending date for the current accounting month, based on the current date.

One of my readers, Mark Connolly, provided me with some code to calculate the accounting months. I then improved on his code slightly so it would work for any account period. Here is Mark’s code with a few minor modifications for calculating Account Months 5-4-4:

DECLARE @CURRENTDATE DATETIME
DECLARE @CURRENTTRIMDATE DATETIME
DECLARE @ANCHORDATE DATETIME
DECLARE @DAYCOUNT AS INT
DECLARE @DAYCOUNT1 AS INT
DECLARE @QSTARTDATE AS DATETIME
DECLARE @QENDDATE AS DATETIME
DECLARE @MSTART AS DATETIME 
DECLARE @MEND AS DATETIME
SET @CURRENTDATE = GETDATE()
-- GET FIRST TIME INTERVAL ON CURRENT DATE
SET @CURRENTTRIMDATE = DATEADD(DD, DATEDIFF(DD,0,@CURRENTDATE), 0)
-- KNOWN START DATE FOR FINANCIAL YEAR
SET @ANCHORDATE = '2007-01-01'
-- NUMBER OF DAYS FROM CURRENTDATE TO KNOWN START DATE FOR FINANCIAL YEAR
SET @DAYCOUNT1 = DATEDIFF (DD,@ANCHORDATE,DATEADD(DD, DATEDIFF(DD,0,@CURRENTTRIMDATE), 0))
-- FIRST DAY OF QUARTER
SET @QSTARTDATE = DATEADD (DD,91*((@DAYCOUNT1-(CASE WHEN @DAYCOUNT1 < 0 THEN 90 ELSE 0 END))/91),@ANCHORDATE)
-- LAST DAY OF QUARTER
SET @QENDDATE = DATEADD(MS,-3,DATEADD (DD,91,@QSTARTDATE))
-- NUMBER OF DAYS FROM QUARTER START DATE AND KNOWN START DATE FOR FINANCIAL YEAR
SET @DAYCOUNT = DATEDIFF(DD,@QSTARTDATE,@CURRENTTRIMDATE)
-- SET MONTH START DATE
SET @MSTART = CASE
                -- CURRENT DATE IN FIRST 5 WEEKS OF QUARTER
                WHEN @DAYCOUNT <= 34 THEN @QSTARTDATE 
                -- CURRENT DATE IN SECOND 4 WEEKS OF QUARTER
                WHEN @DAYCOUNT BETWEEN 35 AND 62 THEN DATEADD(WW,5,@QSTARTDATE)
                -- CURRENT DATE IN LAST 4 WEEKS OR QUARTER
                WHEN @DAYCOUNT >= 63 THEN DATEADD(WW,9,@QSTARTDATE)
                END
SET @MEND = CASE
                -- CURRENT DATE IN FIRST 5 WEEKS OF QUARTER
                WHEN @DAYCOUNT <= 34 THEN DATEADD(MS,-3,DATEADD(WW,5,@QSTARTDATE))
                -- CURRENT DATE SECOND 4 WEEKS OF QUARTER                
                WHEN @DAYCOUNT BETWEEN 35 AND 62 THEN DATEADD(MS,-3,DATEADD(WW,9,@QSTARTDATE))
                -- CURRENT DATE IN LAST 4 WEEKS OR QUARTER
                WHEN @DAYCOUNT >= 63 THEN DATEADD(MS,-3,DATEADD(WW,13,@QSTARTDATE)) 
                END
-- PRINT CALCULATED DATES
SELECT
@CURRENTDATE AS 'NOW',
@CURRENTTRIMDATE AS 'TRIMMED DATE',
@ANCHORDATE AS 'ANCHOR DATE',
@DAYCOUNT1 AS 'DAYS SINCE ANCHOR DATE',
@QSTARTDATE AS 'QUARTER START',
@QENDDATE AS 'QUARTER END',
@MSTART AS 'MONTH START', 
@MEND AS 'MONTH END'

This code will calculate the account 5-4-4 dates for all accounting quarters regardless of the @ANCHORDATE setting. The only requirement is that the @ANCHORDATE needs to be set to the first day of one of your known financial years. In this example, January 2, 2006 is the first day of the financial year. If your financial year starts in July then you would set it to something like July 3, 2006.

I’m sure you can understand most of the above, code so I’m only going to review just a few aspects of this code. In order to make all this code work it was critical to calculate the first day of the current accounting quarter. To do that I first needed to calculate the number of days from the current date and the @ANCHORDATE date, giving the @DAYCOUNT1 variable value. Then I use the following formula to calculate the first day of the current quarter:

DATEADD (DD,91*((@DAYCOUNT1-(CASE WHEN @DAYCOUNT1 < 0 THEN 90 ELSE 0 END))/91),@ANCHORDATE)

Let me break down the important part of this formula, the second parameter of the DATEADD function, and explain what I am doing. First, I need to determine how many quarters there have been since the @ANCHORDATE, and the current date. Remember now, @DAYCOUNT1 is the number of days since the @ANCHORDATE. Therefore, to calculate the number of quarters from the first day of the known financial year I would use the following calculation:

(@DAYCOUNT1-(CASE WHEN @DAYCOUNT1 < 0 THEN 90 ELSE 0 END))/91

Here I first subtract either 90, or 0 from the @DAYCOUNT1 value depending on whether the @DAYCOUNT1 value is positive or negative. A negative value means the current date is less then the known first day of the financial year, a positive number means the current date is either equal to or greater than the known date. So why are we subtracting 90 when the current date is less then the known date? That is because we need to adjust all negative @DAYCOUNT1 values by 90 days so when dividing by the number of days in a quarter (91) the formula calculates the appropriate number of quarters since the known date. If I didn’t adjust the negative values, all dates prior to the known date would have calculated a quarter difference 1 less then they should. Let me use an example to more clearly demonstrate this issue. If I use ’12-31-2006’ as the current day, which is -1 day from the known date, then when I divide -1 by 91 I would end up with 0. But we know ’12-31-2006’ is -1 quarters from the known day. So by subtracting 90 days from ’12-31-2006’ we get -1 (-91/91) when calculating the number of quarters from the known date. Now by just multiplying the number of quarters by 91, I know the number days to add to the @ANCHORDATE to come up with the first day of the financial quarter. Keep in mind all dates prior to the known date will have a negative number to add. Now I can use this quarter start date in all my other date calculations.

The start and end for the current accounting month (@mstart, @mdate) are calculated using a CASE clause. Depending on the number of days (@DAYCOUNT) into the quarter that the current date falls, the CASE clause determines what account month the date falls into, and then sets the account month dates appropriately. Setting the dates is calculated by adding the appropriate number of weeks to the first day of the accounting quarter. And then when calculating the account month end date -3 milliseconds is subtract, so that the quarter end date will end up containing a datetime value representing the time up to the last time interval possible for the month.

Conclusion

Here I have shown you a number of different techniques for calculating different days of the week, and one example on how to calculate the 5-4-4 account month dates. All of these code samples used a common technique that is probably worth mentioning, and that is using a known date, with known characteristics, like first Monday of a month, or first day of the financial year. This method helps build date calculations that are centered around a known date, with known characteristics. This technique and the other different methods I used should give you some ideas how to take the current date and manipulate it with different date functions to produce the date you need to calculate. Next time you need a specific date that is generated based on the current date, try to take the techniques shown here to help formulate your T-SQL code to produce the date you desire.

» See All Articles by Columnist Gregory A. Larsen