Converting Working Days To Calendar Days Using SQL

In the Using SQL To Find Work Days In Date Range post I mentioned that I original thought it would be an easy conversion from calendar days to work days as using simple mathematical formula in SQL, you can get calendar days or a calendar date from working days. It was my thought that the opposite of this formula would work; however, that is another post you can read at your leisure. In this post, I figured I would share my simply formula. It is so straight forward that hopefully it will save you from wasting time going for a more complex approach.

Here is the code listing:

DECLARE @workDays int, @calDays int
SET @workDays = 3

-- using integer division to convert work weeks to calendar weeks
-- AND modulus division to get partial week's days
SELECT @calDays = @workDays / 5 * 7 + @workDays % 5
-- just double check that end result is not on a weekend
WHILE DatePart(dw, DateAdd(dd, @calDays, getdate())) IN (7, 1)
SET @calDays = @calDays + 1
-- select away you have your calendar days and date if you would like
SELECT DateAdd(dd, @calDays, getdate())

As I said, pretty straight forward. Since SQL, at least in Microsoft SQL Server, will treat division of int datatypes as integer division, you will get the whole number amount of weeks represented by work days (i.e. 8/5 = 1). For other platforms, just use FLOOR function or its equivalent in your code (e.g. Floor(@workDays / 5) or the integer division operator to achieve the same results. Subsequently, modulus is taken of working days to get the partial week days involved which should be 0 - 4 that we can add to calendar days we calculated from multiplying the number of weeks by 7.

You can just run in query window as above, but to complement our function/procedure to get working days from a set of calendar dates you can put this in a stored procedure or user defined function the returns the calendar days themselves or the resulting date.

Keep evolving development!