Calculate First and last day of week/month in SQL

Problem

SQL Issue

We face the problem of how to find out the first day and last day of a week/month in SQL 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:

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

To learn more about SQL, follow our blog.