SQL Server Portal

Sometimes, we come across a case where we are given one date and we need to calculate multiple dates based on this date. This could 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 

First Date of Previous Quarter

• Old Approach

In the old approach, we had to extract first date of the previous quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date							   AS [Current Date]
	 , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) -1, 0)) AS [First Date Of Previous Quarter];
GO
--OUTPUT

• New Approach

In the new approach, we can extract the first date of the previous quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
   
SELECT @Date                      AS [Current Date]
     , DATEADD(QUARTER, -1 
     , DATETRUNC(QUARTER, @Date)) AS [First Date Of Previous Quarter]; 
GO
--OUTPUT

Last Date of Previous Quarter

• Old Approach

In the old approach, we had to extract last date of the previous quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date								AS [Current Date]
     , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +0, -1)) AS [Last Date Of Previous Quarter];
GO
--OUTPUT

• New Approach

In the new approach, we can extract last date of the previous quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                      AS [Current Date]
     , DATEADD(DAY, -1
     , DATETRUNC(QUARTER, @Date)) AS [Last Date Of Previous Quarter];
GO
--OUTPUT

First Date of Current Quarter

• Old Approach

In the old approach, we had to extract first date of the current quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE()

SELECT @Date							   AS [Current Date]
	 , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +0, 0)) AS [First Date Of Current Quarter];
GO
--OUTPUT

• New Approach

In the new approach, we can extract the first date of the current quarter using DATETRUNC() function as shown below.

DECLARE @Date DATE;
SET	@Date = GETDATE()

SELECT @Date					 AS [Current Date]
	 , DATETRUNC(QUARTER, @Date) AS [First Date of Current Quarter];
GO
--OUTPUT

Last Date of Current Quarter

• Old Approach

In the old approach, we had to extract last date of the current quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date								AS [Current Date]
     , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +1, -1)) AS [Last Date Of Current Quarter];
GO
--OUTPUT

• New Approach

In the new approach, we can extract the last date of the current quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date					   AS [Current Date]
     , DATEADD(DAY, -1
	 , DATEADD(QUARTER, 1
	 , DATETRUNC(QUARTER, @Date))) AS [Last Date Of Current Quarter];
GO
--OUTPUT

First Date of Next Quarter

• Old Approach

In the old approach, we had to extract first date of the next quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date							   AS [Current Date]
	 , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +1, 0)) AS [First Date Of Next Quarter];
GO
--OUTPUT

• New Approach

In the new approach, we can extract the first date of the next quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                      AS [Current Date]
     , DATEADD(QUARTER, 1
     , DATETRUNC(QUARTER, @Date)) AS [First Date Of Next Quarter];
GO
--OUTPUT

Last Date of Next Quarter

• Old Approach

In the old approach, we had to extract last date of the next quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date								AS [Current Date]
     , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +2, -1)) AS [Last Date Of Next Quarter];
GO
--OUTPUT

• New Approach

In the new approach, we can extract the last date of the next quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date					   AS [Current Date]
     , DATEADD(DAY, -1
	 , DATEADD(QUARTER, 2
	 , DATETRUNC(QUARTER, @Date))) AS [Last Date Of Next Quarter];
GO
--OUTPUT

Conclusion:

We used DATETRUNC() function to achieve it and found it easier & simpler compared to earlier version of SQL Server.

Read Full Post »