none
how to find the weekend days in sql server 2008 RRS feed

  • Question

  • hi,

        I want to find the first weekend day,second weekend day,third weekend day and fourth weekend day per month using sql query.This is getting from recurring appointment.If weekdays = 65 means it accepts only saturday and sunday.So I want first,second,third and fourth weekenddays for a month using query in sql server 2008

    Wednesday, May 13, 2015 9:47 AM

Answers

  • Hi Induma,

    To get the first, second, third and fourth weekend days, you can reference the below sample.

    DECLARE @Start DATETIME ='02/01/2015 02:00:00'
    DECLARE @End DATETIME ='10/03/2015 02:00:00'
    
    SELECT @Start,@END
    
    ;WITH Cte AS
    (
    SELECT @Start DT,DATENAME(WEEKDAY,@Start) WD,1 LEVEL 
    UNION ALL
    SELECT DATEADD(DAY,1,DT), DATENAME(WEEKDAY,DATEADD(DAY,1,DT)),LEVEL+1 FROM CTE
    WHERE DT<@End
    ),
    Cte2 AS (
    SELECT DT,WD,
    LEVEL-ROW_NUMBER() OVER(PARTITION BY DATEADD(MONTH,DATEDIFF(MONTH,0,DT),0) ORDER BY DT) RN,
    DATEADD(MONTH,DATEDIFF(MONTH,0,DT),0) MON 
    FROM CTE WHERE WD IN ('Saturday','Sunday')
    ),
    Cte3 AS
    (
    SELECT DT,WD,DATENAME(MONTH,MON) MON,
    DENSE_RANK() OVER(PARTITION BY MON ORDER BY RN) WD_No FROM CTE2 
    )
    SELECT * FROM CTE3 WHERE WD_No<=4
    OPTION(MAXRECURSION 0)
    
    



    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    Thursday, May 14, 2015 8:58 AM
    Moderator
  • Try this

    ;WITH CTE AS
    (
    SELECT CONVERT(DATE, '01-01-2015') AS [DATE], DATENAME (MONTH,CONVERT(DATE, '01-01-2015')) AS [MONTH],DATENAME (DW,CONVERT(DATE, '01-01-2015')) AS [DAY]
    UNION ALL
    SELECT DATEADD(DAY,1,[DATE]) AS [DATE],DATENAME (MONTH,DATEADD(DAY,1,[DATE])) AS [MONTH],DATENAME (DW ,DATEADD(DAY,1,[DATE])) AS [DAY] FROM CTE WHERE YEAR(DATEADD(DAY,1,[DATE]) )<2016
    )
    SELECT *,ROW_NUMBER ()OVER(PARTITION BY [MONTH],CASE WHEN [DAY] IN ('Saturday','Sunday') THEN 'WEEKEND' ELSE [DAY] END ORDER BY [DATE]) WEEKEND_DAY  FROM CTE 
    WHERE [DAY] IN ('Saturday','Sunday') ORDER BY [DATE]
    OPTION (MAXRECURSION 367)
    

    Result

    • Marked as answer by Induma Friday, May 15, 2015 4:48 AM
    Thursday, May 14, 2015 3:50 PM
  • This kind of question is best solved with a calendar table.  If you google "calendar table", you can find lots of information about how to create them and their uses.  One good site is http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.

    If you create the table using the code supplied in the above link, then to get the weekend days in February, 2015 in order, you would just do

    Select dt
    From dbo.Calendar
    Where Y = 2015 And M = 2 And isWeekday = 0
    Order By dt;
    Tom
    Thursday, May 14, 2015 3:55 PM

All replies

  • Try the below:

    http://blog.sqlauthority.com/2009/12/29/sql-server-get-date-of-all-weekdays-or-weekends-of-the-year/


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    Wednesday, May 13, 2015 9:52 AM
  • Hi,

        This is for one year but i want for individual months

    Wednesday, May 13, 2015 10:22 AM
  • Induma, Thats easy to have a filter or change your FirstdateofYear, Right?

    You may try and let us know for any issues.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    Wednesday, May 13, 2015 10:25 AM
  • create function dbo.NthWeekDay(
       @first datetime,   ---'20150501
       @nth tinyint,      -- Which of them - 1st, 2nd, etc.
       @dow tinyint       -- Day of week we want
    ) returns datetime as begin
    -- Note: Returns a date in a later month if @nth is too large

      declare @result datetime
      set @result = @first + 7*(@nth-1)
      return @result + (7 + @dow - datepart(weekday,@result))%7
    end

    select dbo.NthWeekDay('20150501',1,6)
    select dbo.NthWeekDay('20150501',2,6)
    select dbo.NthWeekDay('20150501',3,6)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 13, 2015 10:36 AM
    Answerer
  • Hi,

    Its easy to change it for Month, below is the query.

    DECLARE @Year AS INT, @Month AS varchar(2), @Day AS varchar(2),
    @FirstDateOfMonth DATETIME,
    @LastDateOfMonth DATETIME
    -- You can change @year to any year you desire
    SELECT @year = YEAR(GETDATE())
    Select @Month = '6'
    Select @Day = Case When @Month IN (1,3,5,7,8,10,12) Then '31' When @Month IN (4,6,8,11) Then '30' Else '28' END
    SELECT @FirstDateOfMonth = @Month + '/1/2015' 
    SELECT @LastDateOfMonth =  @Month + '/' + @Day + '/2015' 

     --Creating Query to Prepare Year Data
    ;WITH cte AS (
    SELECT 1 AS DayID,@FirstDateOfMonth AS FromDate,
    DATENAME(dw, @FirstDateOfMonth) AS Dayname
    UNION ALL 
    SELECT cte.DayID + 1 AS DayID,
    DATEADD(d, 1 ,cte.FromDate),
    DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
    FROM cte
    WHERE DATEADD(d,1,cte.FromDate) <= @LastDateOfMonth
    )

    SELECT FromDate AS Date, Dayname
    FROM CTE
    WHERE DayName IN ('Saturday','Sunday')


    Wednesday, May 13, 2015 10:40 AM
  • Hi frd,

       Are you worked in scheduler concept.In that Recurrence Field,we having some key values.Based on that i want to find the first,second,third and fourth weekend of individual months.

    For example: The second weekend of every 1 month.

    In Recurrence Field:<RecurrenceInfo Start="01/01/2015 02:00:00" End="10/03/2015 02:00:00" WeekOfMonth="2" WeekDays="65" Id="5bf7fbd0-523a-44a4-9637-570d63e8b2a6" OccurrenceCount="10" Type="2" />

    In this,Weekdays = 65 contains saturday and sunday.

    Wednesday, May 13, 2015 1:09 PM
  • Hi frd,

            If you try the answer then rply me soon

    Thursday, May 14, 2015 5:53 AM
  • Hi Induma,

    To get the first, second, third and fourth weekend days, you can reference the below sample.

    DECLARE @Start DATETIME ='02/01/2015 02:00:00'
    DECLARE @End DATETIME ='10/03/2015 02:00:00'
    
    SELECT @Start,@END
    
    ;WITH Cte AS
    (
    SELECT @Start DT,DATENAME(WEEKDAY,@Start) WD,1 LEVEL 
    UNION ALL
    SELECT DATEADD(DAY,1,DT), DATENAME(WEEKDAY,DATEADD(DAY,1,DT)),LEVEL+1 FROM CTE
    WHERE DT<@End
    ),
    Cte2 AS (
    SELECT DT,WD,
    LEVEL-ROW_NUMBER() OVER(PARTITION BY DATEADD(MONTH,DATEDIFF(MONTH,0,DT),0) ORDER BY DT) RN,
    DATEADD(MONTH,DATEDIFF(MONTH,0,DT),0) MON 
    FROM CTE WHERE WD IN ('Saturday','Sunday')
    ),
    Cte3 AS
    (
    SELECT DT,WD,DATENAME(MONTH,MON) MON,
    DENSE_RANK() OVER(PARTITION BY MON ORDER BY RN) WD_No FROM CTE2 
    )
    SELECT * FROM CTE3 WHERE WD_No<=4
    OPTION(MAXRECURSION 0)
    
    



    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    Thursday, May 14, 2015 8:58 AM
    Moderator
  • Hi,

        I want feb 1,2015 (sunday) as first weekend day

                   feb 7,2015 (saturday) as second weekend day

                   feb 8,2015(sunday) as third weekend day

                   feb 14,2015 (saturday) as fourth weekend day.

          And for all month i want this manner only.Pls help me to achieve this way of getting week end day.

    Thursday, May 14, 2015 11:01 AM
  • Try this

    ;WITH CTE AS
    (
    SELECT CONVERT(DATE, '01-01-2015') AS [DATE], DATENAME (MONTH,CONVERT(DATE, '01-01-2015')) AS [MONTH],DATENAME (DW,CONVERT(DATE, '01-01-2015')) AS [DAY]
    UNION ALL
    SELECT DATEADD(DAY,1,[DATE]) AS [DATE],DATENAME (MONTH,DATEADD(DAY,1,[DATE])) AS [MONTH],DATENAME (DW ,DATEADD(DAY,1,[DATE])) AS [DAY] FROM CTE WHERE YEAR(DATEADD(DAY,1,[DATE]) )<2016
    )
    SELECT *,ROW_NUMBER ()OVER(PARTITION BY [MONTH],CASE WHEN [DAY] IN ('Saturday','Sunday') THEN 'WEEKEND' ELSE [DAY] END ORDER BY [DATE]) WEEKEND_DAY  FROM CTE 
    WHERE [DAY] IN ('Saturday','Sunday') ORDER BY [DATE]
    OPTION (MAXRECURSION 367)
    

    Result

    • Marked as answer by Induma Friday, May 15, 2015 4:48 AM
    Thursday, May 14, 2015 3:50 PM
  • This kind of question is best solved with a calendar table.  If you google "calendar table", you can find lots of information about how to create them and their uses.  One good site is http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.

    If you create the table using the code supplied in the above link, then to get the weekend days in February, 2015 in order, you would just do

    Select dt
    From dbo.Calendar
    Where Y = 2015 And M = 2 And isWeekday = 0
    Order By dt;
    Tom
    Thursday, May 14, 2015 3:55 PM
  • Tom is right, calendar tables are the best way to solve this. I wrote an article here on them. Part of the article discusses my 'phNthDayOfMonth' function which might well suit your needs too.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, May 14, 2015 4:26 PM
  • Hi Bharath,

             Thanks for your reply.I need same think for particular month.like january,february,march,.....Now you give for one year and i need for one month.How i can achieve it?

    Friday, May 15, 2015 5:19 AM
  • You can do it this way, it will always extend out to the end of the next year, but you really should look at calendar tables.

    WITH dates AS (
    SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS date
    UNION ALL
    SELECT DATEADD(DAY,1,date)
      FROM dates
     WHERE DATEPART(YEAR,DATEADD(DAY,1,date)) < DATEPART(YEAR,CURRENT_TIMESTAMP) + 2
    )
    
    
    
    SELECT date, ROW_NUMBER() OVER (PARTITION BY DATEPART(MONTH,date), DATEPART(YEAR,date) ORDER by date) AS sequence
      FROM dates
     WHERE DATEPART(WEEKDAY,date) IN (1,7)
     ORDER BY date
     OPTION (MAXRECURSION 0)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Friday, May 15, 2015 2:13 PM
  • Hi Bharath,

             Thanks for your reply.I need same think for particular month.like january,february,march,.....Now you give for one year and i need for one month.How i can achieve it?

    Try This:

    Change the @DATE to '02-01-2015' for February and '03-01-2015' for March .....

    DECLARE @DATE DATE = '01-01-2015'
    
    ;WITH CTE AS
    (
    SELECT @DATE AS [DATE], DATENAME (MONTH,@DATE) AS [MONTH],DATENAME (DW,@DATE) AS [DAY]
    UNION ALL
    SELECT DATEADD(DAY,1,[DATE]) AS [DATE],DATENAME (MONTH,DATEADD(DAY,1,[DATE])) AS [MONTH],DATENAME (DW ,DATEADD(DAY,1,[DATE])) AS [DAY] FROM CTE WHERE DATEADD(DAY,1,[DATE])<DATEADD(MONTH ,1,@DATE)
    )
    
    
    SELECT *,ROW_NUMBER ()OVER(PARTITION BY [MONTH],CASE WHEN [DAY] IN ('Saturday','Sunday') THEN 'WEEKEND' ELSE [DAY] END ORDER BY [DATE]) WEEKEND_DAY  FROM CTE 
    WHERE [DAY] IN ('Saturday','Sunday') ORDER BY [DATE]
    


    Thanks, Bharath bharath-msbi.blogspot.com

    Friday, May 15, 2015 3:08 PM