More help on dates adding only if not week day

More on my dates problem
My start date is 1st July 2005 if i take away only mon-fri dates i should get 20th June 2005 i get 22nd June

SET_PROMISE_DATE =  7/1/2005
@SET_DEFAULT_CUTSEW_DAYS = 9

 Select @CutSewDate = DATEADD(DD, -(@SET_DEFAULT_CUTSEW_DAYS), @SET_PROMISE_DATE)      
 but i need to only take 5 weeke days from the promise date that are week days

-- CHECK NOT A SAT OR SUNDAY
   SELECT @SET_PROMISE_DAY = DATEPART(WEEKDAY, @CutSewDate)
---Sunday take away 2
if @SET_PROMISE_DAY = '1'
      BEGIN
                   Select @CutSewDate = DATEADD(DD, -(@SET_DEFAULT_SUNDAY), @CutSewDate)
      END
--Saturday
if @SET_PROMISE_DAY = '7'
      BEGIN
         Select @CutSewDate = DATEADD(DD, -(@SET_DEFAULT_SATURDAY), @CutSewDate)
      END

I end up with 22 June 2005


How can i solve this .....



LVL 7
TRACEYMARYAsked:
Who is Participating?
 
adwisemanCommented:
Here's your script, with a few changes to use week to find out how many weekends are between your dates.


DECLARE @PROMISE_DATE DATETIME
DECLARE @PROMISE_DAY INT
DECLARE @WeekDayDiff INT
DECLARE @DEFAULT_CUTSEW_DAYS INT
DECLARE @CutSewDate DATETIME
SET @PROMISE_DATE =  '7/1/2005'
SET @DEFAULT_CUTSEW_DAYS =  9

SELECT @WeekDayDiff = (DATEPART(wk, @PROMISE_DATE) - DATEPART(wk, DATEADD(DD, -(@DEFAULT_CUTSEW_DAYS), @PROMISE_DATE))) * 2

Select @CutSewDate = DATEADD(DD, -(@DEFAULT_CUTSEW_DAYS) - @WeekDayDiff, @PROMISE_DATE)    

-- CHECK NOT A SAT OR SUNDAY
   SELECT @PROMISE_DAY = DATEPART(WEEKDAY, @CutSewDate)
---Sunday take away 2
if @PROMISE_DAY = '1'
     BEGIN
                   Select @CutSewDate = DATEADD(DD, 2, @CutSewDate)
     END
--Saturday
if @PROMISE_DAY = '7'
     BEGIN
        Select @CutSewDate = DATEADD(DD, 1, @CutSewDate)
     END

print @PROMISE_DATE
print @WeekDayDiff
print @PROMISE_DAY
print @CutSewDate
0
 
rafranciscoCommented:
>> I end up with 22 June 2005 <<

You will end up with 22-June-2005 because 9 days before July 1, 2005 is June 22, 2005, which is not a weekend.  What do you want to do?  Where did the 9 in the @SET_DEFAULT_CUTSEW_DAYS come from?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You are testing if 22 June 2005 is Saturday or Sunday, that's why you don't end at 20 June 2005.

Try this:

DECLARE @CutSewDate SMALLDATETIME
DECLARE @PROMISE_DATE SMALLDATETIME
DECLARE @DEFAULT_CUTSEW_DAYS INTEGER
DECLARE @PROMISE_DAY INTEGER

SET @PROMISE_DATE =  '7/1/2005'
SET @DEFAULT_CUTSEW_DAYS = 9

Select @CutSewDate = @PROMISE_DATE

print @CutSewDate


WHILE @DEFAULT_CUTSEW_DAYS > 0
  BEGIN
     Select @CutSewDate = DATEADD(DD, -1, @CutSewDate)  

     -- CHECK NOT A SAT OR SUNDAY
     SELECT @PROMISE_DAY = DATEPART(WEEKDAY, @CutSewDate)
     ---Saturday or Sunday take away 1
     if @PROMISE_DAY = 1 OR @PROMISE_DAY = 7
      Select @CutSewDate = DATEADD(DD, -1, @CutSewDate)  
     ELSE
           SET @DEFAULT_CUTSEW_DAYS = @DEFAULT_CUTSEW_DAYS - 1
  END

print @CutSewDate
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TRACEYMARYAuthor Commented:
The 9 is just a default i set up.

I need to only take 9 working days from the promise date
and not include sat and sun when i am doinig the - 9

0
 
Brian CroweDatabase AdministratorCommented:
Here is a function that will subtract only weekdays

CREATE FUNCTION SubtractWeekDays (@date datetime, @days int)
RETURNS datetime
AS
BEGIN

WHILE @days > 0
BEGIN
      SET @date = DATEADD(day, -1, @date)
      IF datepart(weekday, @date) in (2,3,4,5,6)
            SET @days = @days - 1
END
RETURN @date
END
0
 
TRACEYMARYAuthor Commented:
How do i call the function
Thanks
0
 
TRACEYMARYAuthor Commented:
Ok............i got it..............

Awesome..............

Thank you all........so much.

Let me divide the points ............
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.