Sometimes, we come across a case where we have given one date and we need to calculate multiple dates based on this date. This can be achieved in the earlier versions of the SQL Server (earlier than 2022), but we had to use multiple functions like CONVERT(), DATEADD(), DATEDIFF() etc with lots of complexity.
Fortunately, a new function shipped in SQL Server 2022 namely DATETRUNC() which helps us to achieve this scenario easily.
Compatibility Level:
Your database compatibility level MUST be 160 or higher to use this new function.
ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160
Prerequisite:
In order to set the first day of the week as Monday, we need to run the below script.
SET DATEFIRST 1; GO
First Date of Previous Week
- Old Approach
In the old approach, we have to extract first date of the previous week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.
DECLARE @Date DATE; SET @Date = GETDATE(); SELECT @Date AS [Current Date] , CONVERT(DATE , DATEADD(WEEK , DATEDIFF(WEEK, 0, @Date) -1, 0)) AS [First Date Of Previous Week]; GO --OUTPUT
- New Approach
In the new approach, we can extract the first date of the previous week using DATETRUNC() & DATEADD() function as shown below.
DECLARE @Date Date; SET @Date = GETDATE(); SELECT @Date AS [Current Date] , DATEADD(WEEK, -1 , DATETRUNC(WEEK, @Date)) AS [First Date Of Previous Week]; GO --OUTPUT
Last Date of Previous Week
- Old Approach
In the old approach, we have to extract last date of the previous week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.
DECLARE @Date DATE;
SET @Date = GETDATE();
SELECT @Date AS [Current Date]
, CONVERT(DATE
, DATEADD(WEEK
, DATEDIFF(WEEK, 0, @Date) +0, -1)) AS [Last Date Of Previous Week];
GO
--OUTPUT
- New Approach
In the new approach, we can extract the last date of the previous week using DATETRUNC() & DATEADD() function as shown below.
DECLARE @Date DATE; SET @Date = GETDATE(); SELECT @Date AS [Current Date] , DATEADD(DAY, -1 , DATEADD(WEEK, 0 , DATETRUNC(WEEK, @Date))) AS [Last Date Of Previous Week]; GO --OUTPUT
First Date of Current Week
- Old Approach
In the old approach, we have to extract first date of the current week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.
DECLARE @Date DATE; SET @Date = GETDATE(); SELECT @Date AS [Current Date] , CONVERT(DATE , DATEADD(WEEK , DATEDIFF(WEEK, 0, @Date) +0, 0)) AS [First Date Of Current Week]; GO --OUTPUT
- New Approach
In the new approach, we can extract the first date of the current week using DATETRUNC() function as shown below.
DECLARE @Date Date; SET @Date = GETDATE(); SELECT @Date AS [Current Date] , DATETRUNC(WEEK, @Date) AS [First Date of Current Week]; GO --OUTPUT
Last Date of Current Week
- Old Approach
In the old approach, we have to extract last date of the current week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.
DECLARE @Date DATE;
SET @Date = GETDATE();
SELECT @Date AS [Current Date]
, CONVERT(DATE
, DATEADD(WEEK
, DATEDIFF(WEEK, 0, @Date) +1, -1)) AS [Last Date Of Current Week];
GO
--OUTPUT
- New Approach
In the new approach, we can extract the last date of the current week using DATETRUNC() & DATEADD() function as shown below.
DECLARE @Date DATE;
SET @Date = GETDATE()
SELECT @Date AS [Current Date]
, DATEADD(DAY, -1
, DATEADD(WEEK, 1
, DATETRUNC(WEEK, @Date))) AS [Last Date Of Current Week];
GO
--OUTPUT
First Date of Next Week
- Old Approach
In the old approach, we have to extract first date of the next week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.
DECLARE @Date DATE; SET @Date = GETDATE(); SELECT @Date AS [Current Date] , CONVERT(DATE , DATEADD(WEEK , DATEDIFF(WEEK, 0, @Date) +1, 0)) AS [First Date Of Next Week]; GO --OUTPUT
- New Approach
In the new approach, we can extract the first date of the next week using DATETRUNC() & DATEADD() function as shown below.
DECLARE @Date DATE; SET @Date = GETDATE(); SELECT @Date AS [Current Date] , DATEADD(WEEK, 1 , DATETRUNC(WEEK, @Date)) AS [First Date Of Next Week]; GO --OUTPUT
Last Date of Next Week
- Old Approach
In the old approach, we have to extract last date of the next week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.
DECLARE @Date DATE;
SET @Date = GETDATE();
SELECT @Date AS [Current Date]
, CONVERT(DATE
, DATEADD(WEEK
, DATEDIFF(WEEK, 0, @Date) +2, -1)) AS [Last Date Of Next Week];
GO
--OUTPUT
- New Approach
In the new approach, we can extract the last date of the next week using DATETRUNC() & DATEADD() function as shown below.
DECLARE @Date DATE;
SET @Date = GETDATE();
SELECT @Date AS [Current Date]
, DATEADD(DAY, -1
, DATEADD(WEEK, 2
, DATETRUNC(WEEK, @Date))) AS [Last Date Of Next Week];
GO
--OUTPUT
Conclusion:
We used DATETRUNC() function to achieve it and found it easier & simpler as compared to earlier version of SQL Server.
SQL Server Portal 