Query to find First and Third Saturday of a month
In this article, I am going to explain
How to find number of sundays between two dates using SQL,
How to find number of days in a month excluding Sundays using SQL,
Get number of weekdays between two dates in SQL,
Query to find First and Third Saturday of a month
Query to find 100th Day of a year
Which dates Saturday's and sunday's occurs in a month
At first I display all Dates of the year with its Day.
I moved it into a temporary table.
Declare @StartDay datetime, @EndDay datetime
Set @StartDay='2013-01-01'
Set @EndDay='2013-12-31'
;with cte(Date) as
(
select @StartDay
union all
select Date+1 from cte where Date < @EndDay
)
select Date,DATENAME(W,Date)Day into #calendar from cte option (MAXRECURSION 400)Number Of Sundays in the year
select COUNT(*)[Number Of Sundays] from #calendar where Day='Sunday'Number of days in a month excluding Sundays
select COUNT(*)[NoOfDays Excluding Sundays] from #calendar
where Day<>'Sunday' and MONTH(Date)=01Sundays In January
select Date [Sundays In January] from #calendar where Day='Sunday' and MONTH(Date)=1First and Third Saturday in a Month
select * from (
select *,ROW_NUMBER()over (partition by Day order by Date)week from #calendar
where Day ='saturday' and MONTH(Date)=3
)a where week in (1,3)100th Day of a year
select Date [100th Day of a year] from (
select *,ROW_NUMBER()over (order by Date)rn from #calendar
)a where rn=100Saturday's and sunday's in a month
select * from #calendar
where Day in ('saturday','Sunday') and month(date)=6