How do i count SUNDAY's in a particular MONTH

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hello friends,
i have developed one application on "Employee Attendance" for which i want to calculate sundays in a particular month ex.november. how do do it. no matter whether it's in .net or in SQL. i just want to calculate sundays.

and i want it to automatically insert "HOLIDAY" in the database on these days. for all the employees. i have attendance table in which i took fields like attendance date, time, status(whether Present, Absent, Holiday).

i have tried to calculate in .net as well as in SQL but it didn't worked for me.
Any kind of help is appreciated.

Thanks

Posted 29-Nov-11 21:28pm

Jαved

Updated 9-Sep-18 10:05am

Add a Solution

Comments

Jαved 30-Nov-11 4:16am

how do i automatically insert HOLIDAY on sundays in table.

6 solutions

Solution 3

have a look at :
http://www.aspdotnet-suresh.com/2010/09/finding-sundays-in-given-month-using.html[^]

Posted 29-Nov-11 22:00pm

member60

Solution 5

The solution to find out how many Sundays, Mondays etc. in the month.

C#

private int GetOccuranceOfWeekday(int Year, int Month, DayOfWeek Weekday)
{
    int ReturnValue = 0;
    DateTime MyDate = new DateTime(Year, Month, 1);
    int Start = 1;
    if (Weekday != MyDate.DayOfWeek)
    {
        Start = -(MyDate.DayOfWeek - Weekday - 1);
        if (Start <= 0)
        {
            ReturnValue = -1;
        }

    }
    while (Start <= DateTime.DaysInMonth(Year, Month))
    {
        ReturnValue += 1;
        Start += 7;
    }
    return ReturnValue;
}

Just call it like.

C#

MessageBox.Show(string.Format("There are {0} Saturdays in September 2013", GetOccuranceOfWeekday(2013, 9, DayOfWeek.Saturday)));

This May Help.

Posted 23-Sep-13 21:02pm

HMuhammad Kamran

Solution 6

--I tried in Ms-sql

SQL

DECLARE @MONTH INT,@YEAR INT
SET @MONTH=1
SET @YEAR=2018
DECLARE @DATA TABLE
(
ID INT IDENTITY(1,1),
DATE DATE,
WEEKNAME NVARCHAR(10)
)


;WITH CTE
AS
(
SELECT 1 V
UNION ALL 
SELECT V+1 FROM CTE WHERE V+1<=DAY(EOMONTH(DATEFROMPARTS(@YEAR,@MONTH,V)))
)
INSERT INTO @DATA
SELECT DATEFROMPARTS(@YEAR,@MONTH,V),DATENAME(WEEKDAY,DATEFROMPARTS(@YEAR,@MONTH,V)) DATE FROM CTE 

SELECT count(*) cnt FROM @DATA WHERE WEEKNAME='Sunday'

Posted 9-Sep-18 10:05am

Member 13978324

Updated 9-Sep-18 10:20am

v3

Comments

CHill60 10-Sep-18 5:54am

This could be quite an elegant solution with more information provided - I like the way you have used a sequence to generate all dates between the 1st and last days of the selected month.
There is no need for the table variable though, if you generate the list of dates within the CTE itself.
It's also worth pointing out that this would only work in SQL 2012 or greater due to the use of EOMONTH - which wasn't around when the original question was asked.
This would be my version of your solution, demonstrating the points I've made

DECLARE @Start DATE = '01-SEP-2018'
;WITH CTEAS(	
   SELECT @Start V	
   UNION ALL 	
   SELECT V = DATEADD(D, 1, V)  FROM CTE WHERE DATEADD(D, 1, V)
--			<=EOMONTH(@Start) -- SQL 2012 upward	
                       < DATEADD(MM, DATEDIFF(MM, 0, V) + 1, 0) -- SQL 2008 R2 downwards
)
SELECT count(*) cnt FROM CTE WHERE DATENAME(WEEKDAY,V) ='Sunday'

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Savalia Manoj M · Accepted Answer · 2011-11-29T21:59:00

You Can try it in MS-SQL Like

SQL

Declare @Month Bigint
Set @Month =11
Declare @date date;
Set @date='2011-11-01'

while DATEPART(Month,@date)=@Month
Begin
	--Select DATEPART(DAY,@date);
	--Select DATEPART( WEEKDAY, @DATE )
	DECLARE @Name VARCHAR(20)

	SELECT  @Name = CASE ( DATEPART(dw, @Date) + @@DATEFIRST ) % 7
                         WHEN 1 THEN 'Sunday'
                         WHEN 2 THEN 'Monday'
                         WHEN 3 THEN 'Tuesday'
                         WHEN 4 THEN 'Wednesday'
                         WHEN 5 THEN 'Thursday'
                         WHEN 6 THEN 'Friday'
                         WHEN 0 THEN 'Saturday'
                       END 
	If @Name='Sunday'
	Begin
		
		--Insert Data Into Your Table
		Select @date
	End
	
	Set @date=DATEADD(Day,1,@date);	
End

Member 2315574 · Accepted Answer · 2011-11-29T22:13:00

Looks like you want to count Sundays in a particular month. You can use the following C# code.

C#

// The year (1 through 9999).
// The month (1 through 12).
static int CountSundays(int year, int month)
{
	var firstDay = new DateTime(year,month , 1);

	var day29 = firstDay.AddDays(28);
	var day30 = firstDay.AddDays(29);
	var day31 = firstDay.AddDays(30);

	if ((day29.Month == month && day29.DayOfWeek == DayOfWeek.Sunday)
	|| (day30.Month == month && day30.DayOfWeek == DayOfWeek.Sunday)
	|| (day31.Month == month && day31.DayOfWeek == DayOfWeek.Sunday))
	{
		return 5;
	}
	else
	{
		return 4;
	}
}

------------
U Zafar
SE at TradeMeters POS Software ( http://www.TradeMeters.com )

Hemant__Sharma · Accepted Answer · 2011-11-29T21:33:00

Solution 1

http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/8d6c5bec-27ff-4f49-8b24-28f0b4b33d6d[^]

hope this will help

Posted 29-Nov-11 21:33pm

Hemant__Sharma

Comments

Jαved 30-Nov-11 3:44am

Thanks Hemant-Sharma hope this works for me. let me give it a try.

How do i count SUNDAY's in a particular MONTH

6 solutions

Solution 2

Solution 3

Solution 4

Solution 1

Solution 5

Solution 6

Add your solution here

Preview 0

How do i count SUNDAY's in a particular MONTH

6 solutions

Solution 2

Solution 3

Solution 4

Solution 1

Solution 5

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us