Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Need the First Tuesday Beyond Y days after X Date

By Charles Martin,

The task set before my team was to determine the first Tuesday ten days after the Contract Date. At first, we discovered it was a simple calculation, but then it was asked "what about twenty days out?" or "the first Thursday after?" It turned out that simple calculation would not extrapolate to these other scenarios. Therefore, we had to develop a means of calculating on the fly with Y days to acquire the X day of the week.

It becomes necessary at this point to find a pattern and then isolate the formula that would render this pattern consistently. It was realized that the actual pattern was not found in comparing the original date (referred to as StartDate for the remainder of this article) to the resulting day, but the StartDate + Y days compared to the desired day.

The following was a spreadsheet built out showing the StartDate+Y day and how much you have to add to this date to get the desired day of the week:

(B) Desired Date
Sun
(1)
Mon
(2)
Tue
(3)
Wed
(4)
Thu
(5)
Fri
(6)
Sat
(7)
StartDate+Y
Sun (1) 7 1 2 3 4 5 6
Mon (2) 6 7 1 2 3 4 5
Tue (3) 5 6 7 1 2 3 4
Wed (4) 4 5 6 7 1 2 3
Thu (5) 3 4 5 6 7 1 2
Fri (6) 2 3 4 5 6 7 1
Sat (7) 1 2 3 4 5 6 7

Now that we have a pattern, it is necessary to get the right formula to return these results. When we subtract any of the pairs of values, it will return values between six and negative six. Thus, it becomes necessary to subtract the results from 7 or add 1. In this case, to get the reverse patterns seen in the table, we will subtract.

As a basic example, the required formula could look like this using the pair of A and B values:

Algebraically:
7 – ( A – B )
7 – ( 1 – 1 )
7 – 0
7

SQL Code:
SELECT 7 - ( 1 – 1 )
7

This returns the right value, but we should try another value pair such as A = Monday and B = Wednesday just to be certain:

Algebraically:
7 – ( A – B )
7 – ( 2 – 4 )
7 – -2
9

SQL Code:
SELECT 7 - ( 2 – 4 )
9

Now we have a problem. The value should be 2. Thus, we need to get the modulus to render the value down to the correct number. We cannot put the modulus around the whole result as this would return a value between 0-6. We should be able to return the modulus for the subtraction and then subtract from 7 to get the final number.

Algebraically:
7 – [ ( A – B ) % 7 ]
7 – [ ( 2 – 4 ) % 7 ]
7 – [ -2 % 7 ]
7 – 5
2

SQL Code:
SELECT 7 - ( (2 - 4) % 7 )
9

In fact, if we do this in Excel, it WILL return the value of 2. It is in SQL that the result is, once again, 9. Technically, Excel is correct in that the next lowest value that is a multiple of 7 less than say the -1 is -7, the absolute difference between the two values is six. SQL, on the other hand, appears to perform this calculation on the multiple of 7 closest to zero and returns the value as a negative. This is illustrated in the following table:

X Excel
MOD(X,7)
SQL
X % 7
7 0 0
6 6 6
5 5 5
4 4 4
3 3 3
2 2 2
1 1 1
0 0 0
-1 6 -1
-2 5 -2
-3 4 -3
-4 3 -4
-5 2 -5
-6 1 -6
-7 0 0

This is easily resolved by adding seven before performing the modulus operation in SQL and we always end up with the correct values as follows:

Algebraically:
7 – [ ( A – B + 7 ) % 7 ]
7 – [ ( 2 – 4 + 7 ) % 7 ]
7 – [ 5 % 7 ]
7 – 5
2

SQL Code:
SELECT 7 - ( ( 2 - 4 + 7 ) % 7 )
2

So, the final query would look something like this (though it can easily be introduced as a User Defined Function as well):

SQL Code:
DECLARE @StartDate date
DECLARE @DaysOut tinyint
DECLARE @DesiredDayOfWeek tinyint
SET @StartDate = '20110813' --The starting date
SET @DaysOut = 4 --Number of days to count out (the Desired day is AFTER this count)
SET @DesiredDayOfWeek = 6 --The desired day of week

SELECT DATEADD( d,( @DaysOut +( 7 - ( 7 + DATEPART( weekday, DATEADD( d, @DaysOut, @StartDate ) ) - @DesiredDayOfWeek ) ) % 7 ), @StartDate )

This works consistently for any number of days and for any day of the week you need to return. If the desired effect is for the StartDate + Y to be inclusive of that day, then just reduce the value of Y by 1.

Total article views: 1514 | Views in the last 30 days: 1
 
Related Articles
FORUM

Parameter Startdate, Enddate validation

SSRS Parmeters : startdate should not be later than enddate

FORUM

Return result of dynamic query from function

Return result of dynamic query from function

FORUM

Correlated sub query takes more time to return result

Correlated sub query takes more time to return result

ARTICLE

Generating a Sequential Pattern

For many of us, working with the identity property allows us to easily generate a sequential series ...

FORUM

Crosstab not returning desired results

I've read Jeff Moden's article about crosstabs but it does not address my problem

Tags
date    
date manipulation    
modulus    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones