Feeds:
Posts
Comments

SQL Server 2022 – TSQL Enhancement – STRING_SPLIT() Function

December 9, 2022 by Muhammad Imran

In SQL Server 2022, an enhancement came in STRING_SPLIT() function, which was a long awaited functionality. This enhancement provides a new column name ordinal, which has row number for each string split by this function.

Let me create a sample to demonstrate the functionality of new ordinal column in STRING_SPLIT() function as shown below.

Sample:

USE tempdb
GO
   
CREATE TABLE #Employee
(
 EmployeeID     INT IDENTITY(1,1),
 EmployeeName   VARCHAR(500),
 EmailAddresses VARCHAR(500)
);
GO
   
INSERT INTO #Employee(EmployeeName, EmailAddresses)
VALUES
('John', 'John_1@gmail.com;John_2@gmail.com;John_3@hotmail.com'),
('Sarah', 'Sarah_1@gmail.com;Sarah_2@hotmail.com;Sarah_3@gmail.com'),
('Aaron', 'Aaron_1@gmail@com;Aaron_2@hotmail.com'),
('Ackerman', 'Ackerman_1@gmail.com;Ackerman_2@hotmail.com');
GO

SELECT * FROM #Employee;
GO
--OUTPUT

Example 1:

In the below example, I will split the string in email addresses column based on a separator as usual. Also, I will pass the third argument (which came in SQL 2022 as an ehancement) as 1 in STRING_SPLIT() function which means that STRING_SPLIT() will not only split the string but also provide a serial number (ordinal column) against each split string as shown below.

USE tempdb
GO

SELECT EmployeeID
     , EmployeeName
 , value AS EmailAddress
 , Ordinal
FROM   #Employee
CROSS APPLY STRING_SPLIT(EmailAddresses, ';', 1);
GO
--OUTPUT

Example 2:

In this example, I will show you how important is this new column ordinal and how we used to struggle in earlier version of SQL Server (Earlier than 2022) to mimic the same functionality with the help of common table expression & other functions.

Let me grab the first two email addresses for each employee using old and new approach. Both approaches will return the same result but new approach is simple & efficient.

  • Old Approach: (Using ROW_NUMBER() & common table expression)


USE tempdb
GO
 
;WITH CTE AS
(SELECT EmployeeID
      , EmployeeName
      , value AS EmailAddress
      , ROW_NUMBER() OVER(PARTITION BY EmployeeID 
						  ORDER BY EmployeeID ASC) AS Ordinal
FROM    #Employee
CROSS APPLY STRING_SPLIT(EmailAddresses, ';'))
SELECT * FROM CTE WHERE Ordinal<3;
GO
--OUTPUT

  • New Approach: (Using ordinal column)

USE tempdb
GO
 
SELECT EmployeeID
     , EmployeeName
     , value AS EmailAddress
	 , Ordinal
FROM   #Employee
CROSS APPLY STRING_SPLIT (EmailAddresses, ';', 1)
WHERE ordinal<3;
GO
--OUTPUT

Conclusion:

I found the new enhancement of STRING_SPLIT() function very useful, earlier we used common table expression & ROW_NUMBER() functions to find the row number of each split string but now we can easily achieve with the help of STRING_SPLIT() ordinal column. Do let me know if you use ordinal column and how did you find it.

Posted in Functions, SQL SERVER | Tagged , , , , , | 1 Comment »

SQL Server 2022 – TSQL – How to get previous, current and last week dates using DATETRUNC() function

December 8, 2022 by Muhammad Imran

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.

Posted in Solutions, SQL SERVER | Tagged , , , , , , , , , , , | Leave a Comment »

SQL Server 2022 – TSQL – How to get previous, current and last month dates using DATETRUNC() function

December 7, 2022 by Muhammad Imran

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 Month

  • Old Approach

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

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

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

  • New Approach

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

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

SELECT  @Date					 AS [Current Date]
	  , DATEADD(MONTH, -1
	  , DATETRUNC(MONTH, @Date)) AS [First Date Of Previous Month];
GO
--OUTPUT

Last Date of Previous Month

  • Old Approach

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

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

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

  • New Approach

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

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

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

First Date of Current Month

  • Old Approach

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

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

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

  • New Approach

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

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

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

Last Date of Current Month

  • Old Approach

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

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

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

  • New Approach

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

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

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

First Date of Next Month

  • Old Approach

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

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

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

  • New Approach

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

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

SELECT  @Date					 AS [Current Date]
	  , DATEADD(MONTH, 1
	  , DATETRUNC(MONTH, @Date)) AS [First Date of Next Month];
GO
--OUTPUT

Last Date of Next Month

  • Old Approach

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

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

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

  • New Approach

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


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

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

Conclusion:

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

Posted in Solutions, SQL SERVER | Tagged , , , , , , , , , , , | Leave a Comment »

SQL Server 2022 – TSQL – How to get previous, current and last year dates using DATETRUNC() function

December 5, 2022 by Muhammad Imran

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.

Posted in Solutions, SQL SERVER | Tagged , , , , , , , | Leave a Comment »

SQL Server 2022 – TSQL – How to get previous, current and last quarter dates using DATETRUNC() function (final)

December 2, 2022 by Muhammad Imran

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.

Posted in SQL SERVER | Tagged , , , , , , , , , , , | 1 Comment »

SQL Server 2022 – Fix – Error – Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’

December 2, 2022 by Muhammad Imran

STRING_SPLIT() is one of the most used string functions, which splits the string based on one character separator. Earlier I have written an article about it in detail.

Recently, I was using STRING_SPLIT() function and I came across given below error and the reason is I used two characters separator which is NOT valid for STRING_SPLIT() function:

Error :

Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

DECLARE @String AS VARCHAR(50);
SET     @String  = '1:30PM2:30PM3:30PM4:30PM5:30PM6:30PM7:30PM';

SELECT * FROM STRING_SPLIT(@String, 'PM');
GO
--OUTPUT

Solution:

As per SQL documentation, STRING_SPLIT() function can take only one character in the separator argument.

Now, in order to solve this issue, we are going to use REPLACE() function to replace the two characters separator “PM” to one character separator “|“. Also, make sure that you MUST replace with a separator which is NOT available in your data, I used “|” which is not available in my entire data. After that, we can simply split the string using STRING_SPLIT() function as shown in the below example.

--Declare a variable
DECLARE @String AS VARCHAR(50);
 
--Set variable value 
SET @String  = '1:30PM2:30PM3:30PM4:30PM5:30PM6:30PM7:30PM';
 
--Replace PM with another single character separator '|'
SET @String  = LEFT(REPLACE(@String,'PM','|'),LEN(REPLACE(@String,'PM','|'))-1);
 
--Apply String_Split function
SELECT * FROM STRING_SPLIT(@String, '|');
GO
--OUTPUT

Conclusion:

Try to use one character separator in STRING_SPLIT() function, then you will not get above error.

Posted in Errors, SQL SERVER | Tagged , , , , | Leave a Comment »

SQL Server – STRING_AGG() Function

December 1, 2022 by Muhammad Imran

Creating a comma separated list from a table or converting multiple rows into single row along with delimiter was never been easier in earlier (before SQL Server 2017) version of SQL Server. I have written a detailed article back in 2012 regarding this issue, where I used CURSOR, COALESCE() function, STUFF() function & XML format etc. to achieve it.

In SQL Server 2017, a new function shipped namely STRING_AGG() which can help you to achieve above mentioned functionality by using this function ONLY. By the way, instead of comma ( , ) you can use semi-colon ( ; ) or any other delimiters, it works like a charm with all delimiters.

Let me demonstrate STRING_AGG() functionality by giving some examples. I am going to use Customers table in Northwind database as shown below, which can be downloaded from here.

Sample:

As you can see in below sample table that there are many companies in each country.

USE Northwind
GO

SELECT [Country]
     , [CompanyName]
FROM   [dbo].[Customers]
ORDER BY [Country];
GO
--OUTPUT

Example 1:

In the below example, I created a company list separated by comma (delimiter) for each country. You can use any delimiter based on your requirement.

USE Northwind
GO

SELECT [Country]
     , STRING_AGG(CompanyName,', ') AS [CompanyName]
FROM   [dbo].[Customers]
GROUP BY [Country]
ORDER BY [Country];
GO
--OUTPUT

Example 2:

In the above example 1, I got comma-separated list of companies for each country but the issue is those company names do NOT display in an alphabetical sort order. To resolve it we are going to use WITHIN GROUP ORDER BY clause within function as shown below:

USE Northwind
GO

SELECT [Country]
       ,STRING_AGG(CompanyName,', ') WITHIN GROUP (ORDER BY CompanyName) AS [CompanyName]
FROM   [dbo].[Customers]
GROUP BY [Country]
ORDER BY [Country];
GO
--OUTPUT

Example 3:

In this example, I want to get list of comma separated OrderID(s) for each Company but the data does NOT exists in one table, so I will join two tables (Customers, Orders) to be able to  retrieve the data as shown below.

USE Northwind
GO

SELECT [CompanyName] 
	  , STRING_AGG(OrderID,',') AS OrderIDs
FROM   [Customers]
INNER JOIN [Orders] ON [Customers].[CustomerID]=[Orders].[CustomerID] 
GROUP BY [CompanyName];
GO
--OUTPUT

Conclusion:

I use STRING_AGG() function in my day to day SQL scripting quite frequently, I found it very handy. Do let me know if you use this function in your development and how did you find it?

Posted in Functions, SQL SERVER | Tagged , , , , | Leave a Comment »

SQL Server – CONCAT_WS() Function

November 30, 2022 by Muhammad Imran

String concatenation technique is used quite frequently when we are dealing with legacy systems. Sometimes, we need to add a specific delimiter after every column / expression while concatenating multiple columns / expressions. Earlier, we used multiple functions to achieve it and I have written a detailed article about it earlier.

In SQL Server 2017, a new function shipped namely CONCAT_WS() and you can easily achieve the above mentioned scenario by using CONCAT_WS() function. The database compatibility level MUST be 140 or higher to use this function.

Let me create a sample to demonstrate the functionality of CONCAT_WS() function.

Sample:

USE TEST_DB
GO

CREATE TABLE Employees(
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(100),
PrimaryEmail VARCHAR(100),
SecondaryEmail VARCHAR(100),
AlternateEmail VARCHAR(100)
);
GO

INSERT INTO Employees
(EmployeeName, PrimaryEmail, SecondaryEmail, AlternateEmail)
VALUES
 ('Davolio Nancy', 'Davolio2@gmail.com', 'Davolio_Nancy@gmail.com', 'Nancy99@hotmail.com')
,('Fuller Andrew', 'Fuller_12@gmail.com', 'Andrew_f99@gmail.com', 'Andrew_f9@hotmail.com')
,('Leverling Janet', 'Leverling_42@gmail.com', 'j.Leverling_2@gmail.com', 'Janet_Leverling@hotmail.com')
,('Peacock Margaret', 'Peacock@gmail.com', 'Peacock_12@gmail.com', 'Margaret_Peacock@hotmail.com')
GO

SELECT * FROM Employees;
GO
--OUTPUT

Example 1: Concatenate multiple columns & applied semi-colon (;) as a delimiter in between:

  • Old approach using add (+) operator:

In the old approach, when we need to concatenate two or more than two columns using Add (+) operator, we need to manually take care of the concatenation and add delimiter (separator) between columns as shown in the example. This approach was used earlier than SQL Server 2017.

USE TEST_DB
GO

SELECT [EmployeeName]
    , ([PrimaryEmail]+';'+[SecondaryEmail]+';'+[AlternateEmail]) AS [AllEmails]
FROM [Employees];
GO
--OUTPUT

  • New approach using CONCAT_WS() function:

In the new approach, we just need to use CONCAT_WS() function and it will take care of everything as shown below. It also reduces the complexity of the query since we need to apply delimiter once and the function repeats it automatically, as shown below.

Example 2: Concatenate multiple columns & applied space as a delimiter in between:

  • Old approach using add (+) operator:

In this example, we need to use add (+) operator and apply space as a delimiter multiple times in between the columns as shown below.

  • New Approach using CONCAT_WS() funcation :

In this example, we just need to use CONCAT_WS() function with space delimiter at once and it will be applied automatically after each column by the function itself.

Conclusion:

I found CONCAT_WS() function very much useful when concatenating multiple columns / expressions with delimiter. Let me know if you use this function and how did you find it.

Posted in Functions, SQL SERVER | Tagged , , , | Leave a Comment »

SQL Server – CONCAT() Function

November 29, 2022 by Muhammad Imran

Sometimes, we need to concatenate multiple fields to make a consolidated field. These fields may have same data type or multiple data types as well. In the past, we used multiple functions like ISNULL, CONVERT, CAST and used an Add (+) operator to concatenate it.

Now, we have a smart function called CONCAT() to achieve above mentioned scenario easily. This function is intelligent enough that to combine data of single / multiple data types. It converts all the data types to string before joining them together.

Let me show you some examples using old / new approaches.

Example 1: Concatenate multiple columns having NULL values

  • Old Approach Using Add (+) Operator

In old approach, we simply apply add(+) operator to combine multiple fields as shown below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , [Address] + ' ' + [City] + ' ' + [Region] AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

As you can see above, if one of the columns is having NULL value, which you are trying to concatenate, then result will be NULL regardless of which field has value or not.

To resolve NULL issue, we need to use ISNULL function to replace NULL values with empty space and ISNULL should be applied to all columns which we are trying to concatenate, since most of the time, we may not know which column may have NULL values, as shown below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , ISNULL([Address],'') + ' ' + ISNULL([City],'') + ' ' + ISNULL([Region],'') AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

  • New Approach Using CONCAT() Function :

Now, we have seen in earlier approach that how much effort required to concatenate fields. In the new approach, we just need to use a single function which is called CONCAT() function. This function allows us to concatenate multiple columns (varchar, int, datetime etc.) easily as shown in the example below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , CONCAT([Address],' ',[City],' ',[Region]) AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

Example 2: Concatenate multiple columns having different data types

  • Old Approach Using Add (+) Operator

Let’s concatenate multiple data types column using Add (+) operator as shown below but it generates the error since you cannot combine different data types of columns with Add (+) operator, how we were handling such cases earlier.

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , [SupplierID] + ' ' + [ContactName] AS [SupplierID&Name]
FROM  [dbo].[Suppliers];
GO
--OUTPUT

In order to resolve this error, we need to use the CONVERT() function which will convert the int value to nvarchar datatype to make it same data type (string) for both columns and then concatenate it as shown below:

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , CONVERT(NVARCHAR(50)
	 , [SupplierID]) + ' ' + [ContactName] AS [SupplierID&Name]
FROM   [dbo].[Suppliers];
GO
--OUTPUT

  • New Approach Using CONCAT() function:

As we can see many hurdles and complexity in the old approach, in new approcah we need to simply apply CONCAT() function where it itself converts all columns data type to string and then concatenate it and retrun string as an output, as described in below example.

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , CONCAT([SupplierID],' ',[ContactName]) AS [SupplierId&Name]
FROM   [dbo].[Suppliers];
GO
--OUTPUT

Conclusion:

I used CONCAT() function multiple times and found out extremly useful since it automatically handles NULL cases, different data type cases etc. If you use this function, do let me know your experience.

Posted in Functions, SQL SERVER | Tagged , , , , , | Leave a Comment »

SQL Server – String Functions – STRING_SPLIT()

November 28, 2022 by Muhammad Imran

Splitting string in SQL Server was always challenging, we need to create a complex user defined function, using multiple SQL Server built in functions in order to achieve it. I have written an article back in 2012 regarding this. In SQL Server 2016, splitting string became so easy, SQL Server introduced a function to split string namely STRING_SPLIT(), it requires the compatibility level to be at least 130.

Let me create a sample to demonstrate STRING_SPLIT() functionality. In given below sample, I have multiple email addresses separated by a semicolon delimiter against each employee as shown below.

Sample :

USE tempdb
GO
  
CREATE TABLE #Employee
(
 EmployeeID     INT IDENTITY(1,1),
 EmployeeName   VARCHAR(100),
 EmailAddresses VARCHAR(500)
);
GO
  
INSERT INTO #Employee(EmployeeName, EmailAddresses)
VALUES
('John', 'John_1@gmail.com;John_2@gmail.com;John_3@hotmail.com'),
('Sarah', 'Sarah_1@gmail.com;Sarah_2@hotmail.com;Sarah_1@gmail.com'),
('Aaron', 'aaron_1@gmail@com;aaron_2@hotmail.com'),
('Ackerman', 'ackerman_1@gmail.com;ackerman_2@hotmail.com');
GO
  
SELECT * FROM #Employee
GO
--OUTPUT

Now sample is ready, let’s apply STRING_SPLIT() function to see how it works and how easy it is to implement it.

Example 1:

Given below is an example where I used sample created above and split email addresses based on delimiter using STRING_SPLIT() function.

USE tempdb
GO
   
SELECT EmployeeID
     , EmployeeName
     , value AS EmailAddress 
FROM  #Employee
CROSS APPLY 
STRING_SPLIT(EmailAddresses,';')
GO
--OUTPUT

In above records, we have successfully split email addresses and now we have one email address per row. In the above records, though we have successfully split email addresses, still we have duplicate email (Sarah_1@gmail.com) in email address column.

Let’s fix duplicate issue in below example.

Example 2: Removal of duplicate data

In order to remove duplicate email address, we need to use GROUP BY function along with STRING_SPLIT() function in the given below example.

USE tempdb
GO
  
SELECT EmployeeID
     , EmployeeName
     , value AS EmailAddress
FROM  #Employee
CROSS APPLY STRING_SPLIT(EmailAddresses,';')
GROUP BY EmployeeID 
       , EmployeeName
       , value;
GO
--OUTPUT

As you can see above, duplication in email address column has been removed.

Conclusion:

I found STRING_SPLIT() function very useful and we can easily split string without even writing a lot of scripts. You can also implement it wherever it is required without any complexity. Do let me know if you face any issues.

Posted in Functions, SQL SERVER | Tagged , , , , | 4 Comments »

« Newer Posts - Older Posts »