Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    46

    Unanswered: Select weeklyoff dates for the month from sql query

    Hi,

    I want to select all available weeklyoff dated for the month from sql query,

    Please tell me how will i write the query?

    Thanks!

    Prasad

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    What is "Weeklyoff"?

    Do you mean weekends?
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  3. #3
    Join Date
    Jun 2009
    Posts
    46
    Yes it is weekends

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    Code:
    DECLARE @startdate DATETIME, @enddate DATETIME
    SELECT @startdate ='7/1/2009',@enddate ='7/31/2009'
    
    SELECT DATEADD(d,number,@startdate) AS Date INTO #temp
    FROM master..spt_values 
    WHERE TYPE ='p' and number >= 0 
    AND DATEADD(d,number,@startdate) <= @enddate
    
    SELECT DATE AS 'WeeklyOff' FROM #temp 
    WHERE DATENAME(dw,date) ='Saturday' OR DATENAME(dw,date) ='Sunday'
    
    DROP TABLE #temp

  5. #5
    Join Date
    Apr 2007
    Posts
    183
    Code:
    DECLARE @theDate DATETIME
    
    SELECT	@theDate = GETDATE()
    
    SELECT		theDate
    FROM		(
    			SELECT	DATEADD(DAY, Number, DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 0)) AS theDate
    			FROM	master..spt_values 
    			WHERE	TYPE = 'P'
    				AND Number < 32 
    		) AS d
    WHERE		DATEDIFF(MONTH, theDate, @theDate) = 0
    		AND DATEDIFF(DAY, '17530101', theDate) % 7 IN (5, 6)
    ORDER BY	theDate
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •