X
+1 832 290 9522
+91 98250 31415
Book a 30 mins call
SQL

Calculate First and last day of week/month in SQL

Alex
March 26, 2010
My SQL Server

Problem

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:

  • 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

Table of Contents

  1. Problem
  2. Solution
  3. Query
  4. Function

Tags Cloud

Concept Infoway
Recent News Post
.NET Development – For Innovative & Prolific Solutions
Dec12

.NET Development – For Innovative & Prolific Solutions

Need a powerful, secure, and scalable web solutions that look beautiful and offer great productivity? Consider Microsoft .NET...
View More