• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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 .....



0
TRACEYMARY
Asked:
TRACEYMARY
3 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
TRACEYMARYAuthor Commented:
Ok............i got it..............

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

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

Let me divide the points ............
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now