
Calculate First and last day of week/month in SQL
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
Categories
- Android App Development Company7
- Angular Development18
- AngularJs Development1
- App Store Optimization22
- Apple Watch App Development17
- ASP.NET Boilerplate Development18
- ASP.NET Core Development24
- ASP.NET Development34
- ASP.NET Zero Development24
- C# Development23
- CakePHP Development9
- Codeigniter Development13
- CPANEL4
- Craft CMS Development9
- Cross Platform App Development15
- Custom CMS Development14
- Custom CRM Development5
- Custom Development32
- Dot Net Development30
- Drupal Development16
- eCommerce Website Development Services30
- Google Panda 4.21
- HTML 5 Development11
- Internet Marketing SEO6
- iOS App Development Services11
- iPad App Development56
- iPhone App Development55
- Joomla Development16
- Kentico Development20
- Laravel Development16
- Magento Development Services16
- Managed Services4
- Mobile Application Development Company34
- My SQL Server4
- News67
- Next JS Development1
- Offshore Development1
- Open Source Development65
- OpenCart Development3
- Pay Per Click21
- PHP Web Development65
- Power BI Development4
- Quality Assurance11
- React JS Development2
- React Native Web1
- Search Engine Optimization36
- SEO India24
- SharePoint Development22
- Shopify Development Agency8
- Social Media Optimization24
- Software Development India15
- Technology14
- UI/UX Design Company10
- Umbraco Development17
- Uncategorized1
- Wearable App Development8
- Web Application Development10
- Web Design and Development32
- WooCommerce Development15
- WordPress Customization27
- WordPress Development Company25
- Xamarin App Development Company11
- Zen Cart Development11
