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

####
About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

## 6 comments:

awesome work here. i wanted first and last fridays and i did using this query.

thanks once again - Sabnoy

Anonymous,

Thanks for the feedback

This is a very good one!

Thanks so much bro.

Thanks, turned this into 2 functions:

if 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!

Post a Comment