Get Sundays for a Given Month Date in a Function SQL

Get Sundays For a given month Date in a function SQL

I have changed like below and also modified logic required to find sunday's by using datepart function. If you want to return saturday's count also then add it in where clause.

 create function [dbo].[getSundaysandSaturdays]
    (
        @Year int,
        @Month int

    ) 
    RETURNS int
    as 
    begin
    declare     @fdays int 
        ;with dates as
        (
            select dateadd(month,@month-1,dateadd(year,@year-1900,0)) as StartDate
            union all
            select startdate + 1 from dates where month(startdate+1) = @Month
        )
        select @fdays=count(*) from dates where datepart(dw,StartDate) =1
      return @fdays
      end

And Call function like below

  select  dbo.[getSundaysandSaturdays](2015,11)

How to find all first-week days(days up to first Sunday) of the month(date is provided) in the SQL Server?

If you want all days before the first Monday in the month, then you can use a recursive CTE:

with dates as (
     select dateadd(day, 1, eomonth('2020-04-29', -1)) as dte
     union all
     select dateadd(day, 1, dte)
     from dates 
     where datename(weekday, dte) <> 'Sunday'
)
select *, datename(weekday, dte)
from dates;

This assumes that you want 7 days when the week starts on a Monday (although it is easily tweaked to handle that to return no rows.

Here is a db<>fiddle.

Calculate the previous sundays date from a Date column?

SELECT DATEADD(wk, DATEDIFF(wk, 6, Processed_Date), 6) as LastSunday

How do I count the Sundays in a month and select third one?

Try this. The 3rd Sunday, has to be between the 15th and the 21st only.

SELECT CAST(
        CASE
            WHEN [DayOfWeek] = 7 AND DatePart(day,[Date]) between 15 and 21
                THEN 1
            ELSE 0
        END AS bit) as Result, [date], [month]
FROM mytable

How to get total weekend days counts grouped by month for a given month range in SQL Server?

You can use DATEDIFF() and DATENAME() functions along with a recursive CTE such as

DECLARE @date1 AS DATE, @date2 AS DATE;
SET @date1 = '2020-10-01';
SET @date2 = '2020-12-31';

WITH cte AS 
(
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1 AS value
    FROM cte
   WHERE cte.n < DATEDIFF(day,@date1,@date2) 
)
SELECT DATENAME(MONTH,DATEADD(day, n, @date1 ) ) AS [Month Name], COUNT(*) AS [Count]
  FROM cte
 WHERE DATENAME(WEEKDAY, DATEADD(day, n, @date1 ))= 'Sunday'
 GROUP BY DATENAME(MONTH,DATEADD(day, n, @date1 ))

Demo

If the date interval covers more than one year, then use the following code which includes the ordering also.

DECLARE @date1 AS DATE, @date2 AS DATE;
SET @date1 = '2020-10-01';
SET @date2 = '2021-03-31';

WITH cte AS 
(
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1 AS value
    FROM cte
   WHERE cte.n < DATEDIFF(day,@date1,@date2) 
)
SELECT DATEPART(yy, DATEADD(day, n, @date1)) AS [Year],
       DATENAME(MONTH,DATEADD(day, n, @date1)) AS [Month Name], 
       COUNT(*) AS [Count]
  FROM cte
 WHERE DATENAME(WEEKDAY, DATEADD(day, n, @date1))= 'Sunday'
 GROUP BY DATENAME(MONTH,DATEADD(day, n, @date1)),
          DATEPART(yy, DATEADD(day, n, @date1)),
          DATEPART(m, DATEADD(day, n, @date1))
 ORDER BY [Year], DATEPART(m, DATEADD(day, n, @date1))
 OPTION (MAXRECURSION 0)

Demo

---

Related Topics