Count for sundays and count for holidays

siva balan

10y896SQL

I have two doubts .......

1) How to get sundays count for current months...

2) I have one table tblHolidays

ID Holidays Date

1 Krishnanjeyanthi 25-08-2016

2 Vinayagar sathurthi 30-08-2016

3 Deepawali 29-10-2016

How to get this month Holidays count ......

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

ketan borsdiya10y ago
Accepted answer
Hi Siva,

For below code you got all sunday for provided month,
declare @month int, @year int
set @month = 08
set @year = 2016
SELECT
CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number
FROM master..spt_values
WHERE type = 'P'
AND
(CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )
AND
DATENAME(weekday,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number)='Sunday'

For below code you got holidays from your table,
We need to removed sunday from holidays that already available in uppar query,
SELECT CAST([date] as DATETIME) FROM tblHolidays WHERE DATEPART(MONTH,[DATE])=@month AND DATEPART(YEAR,[DATE])=@year AND DATENAME(WEEKDAY, [date])!='Sunday'

Now, You can Union All both query you got all holidays in provided month,
declare @month int, @year int
set @month = 08
set @year = 2016
SELECT
CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number
FROM master..spt_values
WHERE type = 'P'
AND
(CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )
AND
DATENAME(weekday,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number)='Sunday'
UNION ALL
SELECT CAST([date] as DATETIME) FROM tblHolidays WHERE DATEPART(MONTH,[DATE])=@month AND DATEPART(YEAR,[DATE])=@year AND DATENAME(WEEKDAY, [date])!='Sunday'
0

Vinay Singh10y ago

Create Function NumberOfSundays(@dFrom datetime, @dTo   datetime)

Returns int as

BEGIN

   Declare @nSundays int

   Set @nSundays = 0

   While @dFrom <= @dTo Begin

      If datepart(dw, @dFrom) = 1 Set @nSundays = @nSundays + 1

      Set @dFrom = DateAdd(d, 1, @dFrom)

End

   Return (@nSundays)

END

GO

 -- Number of Sundays

Print dbo.NumberOfSundays()

-- Number of Other days

Print DateDiff(d, '2009/06/01', '2009/06/30') + 1 - dbo.NumberOfSundays('2009/06/01', '2009/06/30')

http://forums.asp.net/t/1439809.aspx?find+number+of+sundays+between+two+dates+using+sql

http://stackoverflow.com/questions/6684577/get-number-of-weekdays-sundays-mondays-tuesdays-between-two-dates-sql

Vishal Jadav10y ago
Hi,
Please try below code.
declare@dtstartdate=DATEADD(s,0,DATEADD(mm,DATEDIFF(m,0,GETDATE()),0))
declare@startint=1
declare@dtenddate=DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0))
declare@dnmvarchar(10)=''
while(@dtstart<=@dtend)
begin
set@dnm=datename(dw,@dtstart)
if(@dnm='sunday')
begin
print'Sundayison-'+convert(varchar(10),@dtstart)
end
set@dtstart=dateadd(dd,+1,@dtstart)
end
Hope it helps.
Thanks.....
0
Nitin Sontakke10y ago
I don't believe there is any ready made function which would return number of sundays in a month. You will need to implement a small while loop in a function and pass year and month to it as a parameter. Hopefully, you should be able write one yourself. If not, please do let us know and someone would do it for you.
For getting month-wise holidays from above, use something as follows:
select datepart(mm, [Date]) [MonthNumber], count(1) [Holidays]
from tblHolidays
group by datepart(mm, [Date])
order by datepart(mm, [Date])
0
Midhun Tp10y ago
Hi,
Please check below link for getting sundays in a month -
http://www.aspdotnet-suresh.com/2010/09/finding-sundays-in-given-month-using.html
0