Count for sundays and count for holidays

Question

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 ......

ketan borsdiya · 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(mm1CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) ) AND DATENAME(weekdayCAST(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(mm1CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) ) AND DATENAME(weekdayCAST(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'

Vinay Singh · Answer

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 Jadav · Answer

Hi Please try below code. declare @dtstart date =DATEADD(s0DATEADD(mmDATEDIFF(m0GETDATE())0)) declare @start int =1 declare @dtend date =DATEADD(s-1DATEADD(mmDATEDIFF(m0GETDATE())+10)) declare @dnm varchar (10)= '' while(@dtstart 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.....

Nitin Sontakke · Answer

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])

Midhun T P · Answer

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