X
+1 832 290 9522
+91 9825031415
Calculate First and last day of week/month in 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

sql server tutorials

Categories

Recent News Post
How to amatch whole word in SQL
Mar28

How to amatch whole word in SQL

View More
.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