February 16, 2011

SQL Server: First and Last Sunday of Each Month

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

First Last Day SQL Server

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

First Last Day SQL Server


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:

Anonymous said...

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

thanks once again - Sabnoy

Madhivanan said...

Anonymous,

Thanks for the feedback

Godfrey Prince said...

This is a very good one!
Thanks so much bro.

TraderGordo said...
This comment has been removed by the author.
TraderGordo said...

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

Suprotim Agarwal said...

@TraderGordo: Good job!