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 544, 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:
Datepart

Abbreviation

Year

yy, yyyy

quarter

qq, q

Month

mm, m

dayofyear

dy, y

Day

dd, d

Week

wk, ww

Hour

hh

minute

mi, n

second

ss, s

millisecond

ms

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 TSQL
code that implements this formula.
SELECT DATEADD(DD,
 NUMBER OF DAYS SINCE LAST KNOWN MONDAY AND THE LAST MONDAY OF MONTH
DATEDIFF(DD,
'20061127',
 LAST DAY OF CURRENT MONTH
DATEADD(MS,
3,
 FIRST DAY OF NEXT MONTH
DATEADD(MM,
 NUMBER OF MONTH SINCE 20061201 + 1
DATEDIFF(M,
'20061201',
GETDATE( )
)+1,
'20061201'
)
)
) / 7 * 7,
'20061127'
)
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 20061201 + 1
DATEDIFF(M,
'20061201',
GETDATE( )
)+1,
'20061201'
)
)
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,
'20061127',
<Last Day of Month code above goes here>
) / 7 * 7,
'20061127'
)
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 TSQL 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,
'20061127',
 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,
'20061127'
)
This code is similar to the
example that calculated the last Monday of the month, so I will not explain the
entire set of TSQL 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,
'20061127',
 X NUMBER OF WEEKS FROM THE FIRST DAY OF THE MONTH
DATEADD(DD,
(@WHICHMONDAY * 7) 1,
 TODAY'S DATE
getdate()
)
) / 7 * 7,
'20061127'
)
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,
'20061130',
 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,
'20061130'
)
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 “20061127” to “20061130”. 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 “20061130” 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 544
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 544:
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 = '20070101'
 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 544 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 ’12312006’ 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 ’12312006’ is
1 quarters from the known day. So by subtracting 90 days from ’12312006’ 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 544 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 TSQL code to produce the date you
desire.
»
See All Articles by Columnist Gregory A. Larsen