Calculate First and last day of week/month in SQL

Calculate First and last day of week/month in SQL

Alex
March 26, 2010

Problem

We face the problem of how to find out the first day and last day of a week or a month while developing a website/application.

Solution

This can simply be calculated with the help of SQL queries. We can have a query executed, which ultimately calls a SQL function to do the needful. Please find below the query and the function for the same.

Query

This query calculates the first and the last day of a week, by keeping the current date as the point of reference. The current date can be retrieved directly from SQL, by using its default function getdate().

declare @Sundayofweek datetime,@saturdayofweek datetime
declare @Lastdayoflastmonth datetime,@Firstdayofnextmonth datetime
set @Sundayofweek=(SELECT Sunday FROM dbo.DisplayCurrentWeekDateDays(dbo.Getdate()))
set @saturdayofweek=(SELECT saturday FROM dbo.DisplayCurrentWeekDateDays(dbo.Getdate()))
set @Lastdayoflastmonth=(DATEADD(dd,-(DAY(dbo.Getdate())),dbo.Getdate()))
set @Firstdayofnextmonth=(DATEADD(dd,-(DAY(DATEADD(mm,1,dbo.Getdate()))-1),DATEADD(mm,1,dbo.Getdate())))
The above query can return the following results:

  • Sunday as the first day of the current week
  • Saturday as the last day of the current week
  • The first day of the current month
  • The last day of the current month

Function

This function returns the required day of the week, based on the current date passed as a parameter from the above query.

ALTER FUNCTION [dbo].[DisplayCurrentWeekDateDays]
(@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
BEGIN
DECLARE @day INT
SET @today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
INSERT INTO @WeekDateDay (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
SELECT DATEADD(dd, 1 – @day, @today) Sunday,
DATEADD(dd, 2 – @day, @today) Monday,
DATEADD(dd, 3 – @day, @today) Tuesday,
DATEADD(dd, 4 – @day, @today) Wednesday,
DATEADD(dd, 5 – @day, @today) Thursday,
DATEADD(dd, 6 – @day, @today) Friday,
DATEADD(dd, 7 – @day, @today) Saturday
RETURN
END

Recent News Post
iPhone 14 and iPhone 14 Pro – Let’s Learn More About its Features, Specs, and Prices
Oct11

iPhone 14 and iPhone 14 Pro – Let’s Learn More About its Features, Specs, and Prices

Finally, the wait is over, and the legendary iPhone 14 by Apple Inc. is here to spellbind all...
View More
Hire Qualified ASP.Net Developers to Explore New Business Horizons in 2022
Sep20

Hire Qualified ASP.Net Developers to Explore New Business Horizons in 2022

In 2022, it is fruitful in many ways to hire asp.net developers – ASP.NET development is affordable, secure,...
View More