Adding days to a date in derived column expression

Question

I am using a derived column which takes data from an XML source to put it into a ole db destination.

In the expression I want to calculate an end date using the supplied start date and a supplied duration field.

This would be a simple dateadd expression, however I need to also account for weekends so if any of the days between the calculated dates are a non working day (saturday or sunday) then I need to increase the end date to account for this.

datename(weekday, getdate())  I know will get me the day of week but not sure how I step through the inbetween dates

Answer 1

The first parameter to the DATEADD function is the datepart you want to add to your date. You can use dw or w to add weekdays to your date. Check this for more information on the DATEADD function. So your expression will look something like DATEADD("DW", <duration>, [<date column>])

---

http://btsbee.wordpress.com/

Answer 2

if ( ( DATEPART(dw, getdate()) + @@DATEFIRST) % 7 ) not in (0,1)
print 'weekdays'
else
print 'weekends'

you will get by this rule to get the weekends, and then add those number of days to your date field. You can use this condition in where clause by replacing the getdate() with your date field and then add those days to that field to move ahead.

regards

joon

Answer 3

Hi..

What i understood from you is ..that you want to add number of business days to a start date and get the end date..

You can use this sql stmt..

select dateadd(dd,((@no_of_days/5)*7 +(@no_of_days%5)),@start_date)

Answer 4

I am using a derived column which takes data from an XML source to put it into a ole db destination.

In the expression I want to calculate an end date using the supplied start date and a supplied duration field.

This would be a simple dateadd expression, however I need to also account for weekends so if any of the days between the calculated dates are a non working day (saturday or sunday) then I need to increase the end date to account for this.

datename(weekday, getdate())  I know will get me the day of week but not sure how I step through the inbetween dates

Hi James,

The problem you are facing requires counting each weekend and it would not work in a simple query as your date may go across multiple years.

You can use the following SQL query to get the number of weekend days:

DECLARE @StartDate DATETIME
DECLARE @Duration INT

SET @StartDate = CONVERT(DATETIME, '2012-01-01')
SET @Duration = 6

;WITH Date_Result(DateOfWeek) AS (
    SELECT @StartDate dates
    UNION ALL
    SELECT DATEADD(day, 1, DateOfWeek) DateOfWeek
    FROM Date_Result
    WHERE DateOfWeek < DATEADD(day, @Duration, @StartDate)
)
SELECT COUNT(DateOfWeek) TotalDuration FROM Date_Result
WHERE DATEPART(dw, DateOfWeek) IN (1,7)
OPTION (MaxRecursion 0);

Now you can use this Query in OLEDB command Transformation to get the number of Weekend days and add to the Duration to get the total days required.

One thing you should look ahead is when you add this count to the duration still may not be the required number of days like consider a situation where Start date is 2012-01-01 and your duration is 6 days.

so 6 + 2 weekend days = 8 days but actually they should be 9 as the 7th day is again Sunday which was not taken in account when defining a duration (6).

For that you can add some more code to the SQL that i shared. As for my code, your SQL for OLE DB command Transformation should look like the following:

;WITH Date_Result(DateOfWeek) AS (
    SELECT ? dates
    UNION ALL
    SELECT DATEADD(day, 1, DateOfWeek) DateOfWeek
    FROM Date_Result
    WHERE DateOfWeek < DATEADD(day, ?, ?)
)
SELECT COUNT(DateOfWeek) TotalDuration from Date_Result 
WHERE DATEPART(dw, DateOfWeek) IN (1,7)
OPTION (MaxRecursion 0);

Hope this helps- 

---

Please mark the post as answered if it answers your question