- Counting days elapsed between two dates excluding weekends
- Getting the Thursday (or any other day) of current week
and so on
The most common way of implementing these type of logic in T-SQL involves the use of DATEPART or DATENAME functions using dw (DAYOFWEEK) as the interval argument. Though this will serve the purpose, programmers seldom realise the fact that the logic written in this way is dependent on the server locale and datefirst settings. This will particularly be a critical factor in cases where the code needs to be replicated to multiple servers spanning over multiple geographic regions and having non similar locale settings.
I’ve had the need of implementing a server locale independent logic of late for a similar scenario in a project and came up with the below alternate approach
Scenario 1: Get elapsed days count between two dates
declare @start date=‘20120816’,@end date=‘20120820’
–solution using datename (depends on locale setting)
select datediff(dd,@start,@end) – (datediff(wk,@start,@end)*2 +case when datename(dw,@start) in (‘Saturday’,‘Sunday’) then 1 else 0 end+case when datename(dw,@end) in (‘Saturday’,‘Sunday’) then 1 else 0 end)+1
–solution independent of locale setting
select datediff(dd,@start,@end) – (datediff(wk,@start,@end)*2 +case when datediff(dd,0,@start)%7 >4 then 1 else 0 end+case when datediff(dd,0,@end)%7 >4 then 1 else 0 end)+1
If you change the locale setting to someother value like Italian you can see that first logic will give you an incorrect value because of fact that day names are different in Italian. Similarly usage of DATEPART with dw relies upon DATEFIRST setting.
Analyzing the second query will indicate the fact that solution is independent of DATEPART and DATENAME functions and it relies upon the DATEDIFF function alone.
The logic works as follows.
The base date of 1 Jan 1900 is a Monday (corresponding to integer value of 0). The DATEDIFF will calculate days elapsed since the base date and divides this by 7. This will return us a reminder value which can be used to identify the day (Starting from 0 corresponding to Monday to maximum of 6 for Sunday). So in this case values 5 and 6 corresponds to days Saturday,Sunday respectively irrespective of locale or datefirst setting as we’re using an integer calculation method here.
Scenario 2: Get the Tuesday of current week
SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0) + 1
Analyzing this query we can see that this again utilizes the integer arithmetic rather than the day of week logic. The / 7 * 7 part will cause the days elapsed value to reduce up to the Monday of the week under consideration (as 0th date ie 1 Jan 1900 was a Monday). Adding the same amount of days back from the basedate will return current week Monday’s date. Since we want Tuesday we will add 1 more to that. This code as you see is independent of any locale or language settings and works in any locale server.
Similarly any logic involving the day of week comparison can be rewritten on similar lines to avoid locale/language dependency. Hope this blog will provide the guideline for you in doing that.
As always feel free to post your feedback or any further questions on this. See you with a new blog soon.
