SQL Server Portal

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 

First Date of Previous Year

• Old Approach

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

DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                            AS [Current Date]
     , CONVERT(DATE
     , DATEADD(Year
     , DATEDIFF(Year, 0, @Date) -1, 0)) AS [First Date Of Previous Year];
GO
--OUTPUT

• New Approach

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

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

Last Date of Previous Year

• Old Approach

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

DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                             AS [Current Date]
     , CONVERT(DATE
     , DATEADD(YEAR
     , DATEDIFF(YEAR, 0, @Date) +0, -1)) AS [Last Date Of Previous Year];
GO
--OUTPUT

• New Approach

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

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

First Date of Current Year

• Old Approach

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

DECLARE @Date DATE;
SET @Date = GETDATE();
  
SELECT @Date                         AS [Current Date]
     , CONVERT(DATE
     , DATEADD(YEAR
     , DATEDIFF(YEAR, 0, @Date), 0)) AS [First Date of Current Year];
GO
--OUTPUT

• New Approach

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

DECLARE @Date DATE; 
SET @Date = GETDATE();
 
SELECT @Date                  AS [Current Date]
     , DATETRUNC(YEAR, @Date) AS [First Date of Current Year];
GO
--OUTPUT

Last Date of Current Year

• Old Approach

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

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

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

• New Approach

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

DECLARE @Date DATE
SET @Date = GETDATE()
   
SELECT @Date                    AS [Current Date]
     , DATEADD(DAY, -1
     , DATEADD(YEAR, 1
     , DATETRUNC(YEAR, @Date))) AS [Last Date of Current Year];
GO
--OUTPUT

First Date of Next Year

• Old Approach

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

DECLARE @Date DATE;
SET @Date = GETDATE();
  
SELECT @Date                            AS [Current Date]
     , CONVERT(DATE
     , DATEADD(Year
     , DATEDIFF(Year, 0, @Date) +1, 0)) AS [First Date Of Next Year];
GO
--OUTPUT

• New Approach

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

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

Last Date of Next Year

• Old Approach

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

DECLARE @Date DATE;
SET @Date = GETDATE();
   
SELECT @Date                             AS [Current Date]
     , CONVERT(DATE
     , DATEADD(Year
     , DATEDIFF(Year, 0, @Date) +2, -1)) AS [Last Date Of Next Year];
GO
--OUTPUT

• New Approach

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

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

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

Conclusion:

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

Read Full Post »