Skip to Content
author's profile photo Former Member
Former Member

Querr Different of Days Excluding Weekends

Hi All,

I use below to query different of days between 2 dates. How if I want to query the different of days which will EXCLUDE the weekends.

Also, how to convert the character to integer for calculation if the UDFs was first created as Character?

SELECT datediff(d ,T0.U_Arrivaldate,T0.U_DateToDone) 'remaining days' FROM OSCL T0

WHERE T0.U_Arrivaldate >= T0.U_DateToDone AND

(T0.U_Arrivaldate BETWEEN '[%0]' AND '[%0]' AND T0.U_DateToDone BETWEEN '[%1]' AND '[%1]' )

Hope for answer.. Thanks!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2011 at 04:04 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2011 at 06:12 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2011 at 07:04 AM

    Hi All,

    Those links provided seems not for SBO query.. Any other guidance please? Urgent....

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2011 at 03:31 PM

    Hi,

    Try this first:

    SELECT datediff(d ,T0.U_Arrivaldate,T0.U_DateToDone) 'remaining days' FROM OSCL T0
    WHERE T0.U_Arrivaldate >= T0.U_DateToDone AND
    (T0.U_Arrivaldate BETWEEN '[%0]' AND '[%1]' AND  T0.U_DateToDone BETWEEN '[%0]' AND '[%2]' )

    Your original query only get one date for each field.

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2011 at 01:47 AM

    thanks Gordon, how bout the excluding weekends? any clue? anybody can help please?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Rachel,

      Try this

      CREATE TABLE #result(callID INT,Diff INT)
      
      CREATE TABLE #OSCL(iIndex INT IDENTITY(1,1),callID INT,U_ArrivalDate DATE,U_DateToDone DATE)
      INSERT INTO #OSCL(callID,U_ArrivalDate,U_DateToDone)
      SELECT T0.callID,T0.U_ArrivalDate,T0.U_DateToDone FROM OSCL T0 WHERE T0.U_ArrivalDate>=T0.U_DateToDone 
      AND(T0.U_ArrivalDate BETWEEN '[%0]' AND '[%1]' AND T0.U_DateToDone BETWEEN '[%2]' AND '[%3]')
      ORDER BY callID
      
      DECLARE @iIndex AS INT SET @iIndex=1
      DECLARe @iMax AS INT SELECT @iMax=MAX(iIndex)FROM #OSCL
      
      DECLARE @callID AS INT
      DECLARE @U_ArrivalDate AS DATE
      DECLARE @U_DateToDone AS DATE
      DECLARE @Diff AS INT
      
      WHILE @iIndex<=@iMax
        BEGIN
      	SET @Diff=0
      	SELECT @callID=callID,@U_ArrivalDate=U_ArrivalDate,@U_DateToDone=U_DateToDone 
      FROM #OSCL WHERE iIndex=@iIndex
      
      	WHILE @U_DateToDone<=@U_ArrivalDate
      	  BEGIN
      		IF DATEPART(WEEKDAY,@U_DateToDone)BETWEEN 2 AND 6
      		  BEGIN
      			SET @Diff=@Diff+1
      		  END
      		
      		SET @U_DateToDone=DATEADD(DAY,1,@U_DateToDone)
      	  END
      	
      	INSERT INTO #result(callID,Diff)VALUES(@callID,@Diff)
      	  
      	SET @iIndex=@iIndex+1
        END
        
      SELECT*FROM #result
      
      DROP TABLE #result
      DROP TABLE #OSCL
      

      Best Regards,

      Hendry Wijaya

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.