Calculate First and last day of week/month in SQL

SQL

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

SAVE YOUR TIME AND MONEY

Save your time and money by choosing us, a scalable team with 120+ professionals with expertise in various domains and technologies.

Dedicated TL / PM with Quick Response

Have a dedicated Team Lead / Project Manager for your each project, all with an assurance that your project will get utmost priority, along with the quick and firm response for all your queries regarding it.

QUICK, SECURE AND SWIFT CODE

With expertise on all the latest architectural and coding structures, our experienced developers ensure that the site is secure, and performs quick and swiftly.

Leave us your info

Select Service

Describe your requirement here

Upload An Attachment