This post shows you how to find the First and Last Sunday for each month of a given year, in SQL Server. Let us see the t-sql code first
Here’s the same code for you to try out:
declare @year int
set @year =2011
-- First and Last Sunday by SqlServerCurry.com
select min(dates) as first_sunday,max(dates) as last_sunday from
(
select dateadd(day,number-1,DATEADD(year,@year-1900,0))
as dates from master..spt_values
where type='p' and number between 1 and
DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))
) as t
where DATENAME(weekday,dates)='sunday'
group by DATEADD(month,datediff(month,0,dates),0)
For this example, we are using the table master..spt_values which is a system table and contains a number column that has numbers from 0 to 2047. We can make this as a number table. Alternatively you can also create a large number table and use it in the query.
Based on the the year value given (in our case 2011), the first day of that year should be calculated. The code DATEADD(year,@year-1900,0) will give us the First day of the year @year.
We need to consider all the date values from January 1st of that year to December 31st.
The where condition number between 1 and DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0)) makes sure that all date values for that year are considered.
We then find whether the date is a Sunday by using DATENAME function. As we need the first and last Sunday for each month, we need to group the result by each month. The code DATEADD(month,datediff(month,0,dates),0) used in group by clause, groups the
result by each month.
OUTPUT
awesome work here. i wanted first and last fridays and i did using this query.
ReplyDeletethanks once again - Sabnoy
Anonymous,
ReplyDeleteThanks for the feedback
This is a very good one!
ReplyDeleteThanks so much bro.
This comment has been removed by the author.
ReplyDeleteThanks, turned this into 2 functions:
ReplyDeleteif dbo.isDateFirstXDayOfMonth('11/6/2015','Friday')=1 select 'Yes' else select 'No'
create function isDateFirstXDayOfMonth(@MyDate smalldatetime, @XDay varchar(10))
RETURNS int
AS
BEGIN
--DSG 11/11/2015 You can pass in a date and dayofweek like: getdate(), 'Friday' and the function will return 1 if the date you passed in was the first Friday of the month.
--Example call:
--if dbo.isDateFirstXDayOfMonth('11/6/2015','Friday')=1 select 'Yes' else select 'No'
select @MyDate=CONVERT(char(10), @MyDate,126)
declare @year int
set @year =datepart(yyyy,@MyDate)
declare @retval int
select @retval=case when @MyDate=first_friday then 1 else 0 end from (
select min(dates) as first_friday,max(dates) as last_friday from
(
select dateadd(day,number-1,DATEADD(year,@year-1900,0))
as dates from master..spt_values
where type='p' and number between 1 and
DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))
) as t
where DATENAME(weekday,dates)='friday'
group by DATEADD(month,datediff(month,0,dates),0)
)x where datepart(mm,first_friday)=datepart(mm,@MyDate)
RETURN @retval
END
if dbo.isDateLastXDayOfMonth('12/25/2015','Friday')=1 select 'Yes' else select 'No'
create function isDateLastXDayOfMonth(@MyDate smalldatetime, @XDay varchar(10))
RETURNS int
AS
BEGIN
--DSG 11/11/2015 You can pass in a date and dayofweek like: getdate(), 'Friday' and the function will return 1 if the date you passed in was the last Friday of the month.
--Example call:
--if dbo.isDateLastXDayOfMonth('12/25/2015','Friday')=1 select 'Yes' else select 'No'
select @MyDate=CONVERT(char(10), @MyDate,126)
declare @year int
set @year =datepart(yyyy,@MyDate)
declare @retval int
select @retval=case when @MyDate=last_friday then 1 else 0 end from (
select min(dates) as first_friday,max(dates) as last_friday from
(
select dateadd(day,number-1,DATEADD(year,@year-1900,0))
as dates from master..spt_values
where type='p' and number between 1 and
DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))
) as t
where DATENAME(weekday,dates)='friday'
group by DATEADD(month,datediff(month,0,dates),0)
)x where datepart(mm,last_friday)=datepart(mm,@MyDate)
RETURN @retval
END
@TraderGordo: Good job!
ReplyDelete