In this article, I will guide you on how to delete old backup files on an SQL Server Database. Though we have moved to the cloud, there are still some projects that have their own server machines, on which we do deployment, SQL Server Database backups, and other jobs.

There are two ways we can delete old database backup files:
    Using C# Code (delete files from location)
    Using SQL Script (master.sys.xp_delete_file)

Delete SQL Server Database backup files Using C# Code
Set a path in App.config file.
<appSettings>
        <add key="path" value="C:\WorkingProjects\Practice\DataBase\Employee\DBBackup"/>
</appSettings>

In Program.cs file.
In my case, I have set it to delete database backup files that are older than one week.

Note
    I have used CreationTime, which will return the date and time when the database backup was created.
    .bak is an extension of SQL Server backup file.

var directorypath = ConfigurationManager.AppSettings["path"].ToString();
var files = Directory.GetFiles(directorypath, "*.bak");

foreach (var file in files)
 {
   var _file = new FileInfo(file);
   if (_file.CreationTime < DateTime.Now.AddDays(-7)) // weekly
        _file.Delete(); // Delete File
 }

SQL Server DB backups which we want to delete.

Before Code execution (Before Old DB backup Delete)

After Code execution (After Old DB backup Delete).
Now, we can verify whether our old database backup is deleted or not.

Delete SQL Server Database backup files Using SQL Query (xp_delete_file)
DECLARE @name NVARCHAR(500); -- Database name
DECLARE @path NVARCHAR(500); -- Path for backup files
DECLARE @fileName VARCHAR(500); -- Filename for backup
DECLARE @fileDate VARCHAR(20) = CONVERT(VARCHAR(20),GETDATE(),112); -- Used for file name
DECLARE @FileExtension nvarchar(4) = N'.BAK' -- file extension
DECLARE @DeleteDate DATETIME = DATEADD(wk,-1,GETDATE()); -- last week date

-- Path to backups.
SET @path = 'C:\WorkingProjects\Practice\DataBase\Employee\DBBackup'

-- Dynamically get each database on the server.
DECLARE db_cursor CURSOR FOR

SELECT NAME
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;

-- Loop through the list to backup each database.
    WHILE @@FETCH_STATUS = 0
    BEGIN
          -- Build the path and file name.
          SET @fileName = @path + @name + '_' + @fileDate + @FileExtension;
          --select @fileName

          -- Loop to the next database.
          FETCH NEXT FROM db_cursor INTO @name;
    END

--Delete backup files
EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

xp_delete_file takes five parameters:
    File Type - 0 for backup files or 1 for report files.
    Folder Path - The folder to delete files.
    File Extension - This could be ‘BAK’.
    Date - Cutoff date for what files need to be deleted.
    Subfolder - 0 to ignore subfolders, 1 to delete files in subfolders.

Before Query execution (Before Old DB backup Delete)

After Query execution (After Old DB backup Delete).
Now we can verify whether our old database backup has been deleted or not.

Note
We can add our .exe file in the task scheduler and script in the SQL Job, which we can automate to execute as per our choice (weekly/monthly).

In this article, I will guide you in how to take SQL Server Database backup to a local folder. There are two ways to take a database backup.

Method 1
Open SQL Server Management Studio (SSMS) and follow the below steps
Select the Database that you want to take backup.

For example, here I am using the EmployeeDB database.

Select Database. Right click on database -> select Task -> Back Up.

Once we click on Back up, a pop-up window will open. This window will show the Database name from which we are taking a backup. Select the backup type as Full, and back up to Disk.

Now click on the remove button and then click on Add. This will open one more popup window, which will allow us to choose our specific path. Now once we navigate to our path, we need to provide the database file name. In my case, I have given EmployeeDB.bak. and click on OK.

Note: ".bak" is an extension for backup.

Once we click Ok, our backup path will be set. Now click Ok.

Once we click on OK our database backup will be created to our provided path.

Now we can verify whether our database backup is created or not.

Method 2
Using SQL Query.
declare @backuppath as nvarchar(max)
set @backuppath  = N'C:\WorkingProjects\Practice\DataBase\Employee\EmployeeDB_'
+ CONVERT(nvarchar,YEAR(getdate()))
+ CONVERT(nvarchar,Month(getdate())) +
+ CONVERT(nvarchar,DAY(getdate())) + '.bak'
Backup Database [EmployeeDB] to DISK = @backuppath WITH NOFORMAT, NOINIT, NAME=N'EmployeeDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Now execute the query

Once the query is executed, it will create a database backup to the specified path.

Eg. In my case path is "C:\WorkingProjects\Practice\DataBase\Employee".

Now, we can verify whether our database backup is created or not.

HostForLIFEASP.NET SQL Server 2021 Hosting

T-SQL provides different ways of “gathering” information from tables. Usually in our practice, we use joins, unions, table expression, etc. But, one of the most interesting ways of getting information from tables is done using Subquery.

Preparation
The below examples require using AdventureWorks2019 from Microsoft. You can download it from here.

Let's get started.

First, what is Subquery?
- Subqueries in T-SQL provide a way for retrieving data which relies on some “statistical” information. In case of using subqueries, looking at a query from a building block perspective, it has two main parts:

    Main/Outer query (what will be filtered)
    Secondary query (subquery, the filtering side)

We use subquery in the WHERE clause. Depending on the subquery, we do some filtering on the main query. Subqueries can return single, multiple or table-based data. For a single response, we will use **WHERE colx = ( response from subquery )** pattern. On the other hand, for multiple responses, our pattern would be WHERE colx IN ( response from subquery).

There are two types of subqueries in T-SQL:
    Self-contained subqueries
    Correlated subqueries

Self-contained subqueries
This type of subquery has no dependency on the outer/main query. They can be easily executed in isolated context.

Here are some use cases on how to use self-contained subqueries:
USE AdventureWorks2019;
Go
--Retrieve all SalesOrderDetail info WHERE :
-- 1)unitprice is greater than average unit price in the same table.

SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.UnitPrice > (SELECT AVG(SOD1.UnitPrice) FROM Sales.SalesOrderDetail AS SOD1)

--2)unitprice is less than or equal to minimum lineTotal's and maximum linetotal's sum dividing to 33;
SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOd.UnitPrice <= (SELECT min(SOD1.LineTotal)+max(SOd1.linetotal)/33 FROM Sales.SalesOrderDetail AS SOD1)

--3) DiscountPct for SpecialOffer is less than or equal to 0.30
SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.SpecialOfferID IN (SELECT SO.SpecialOfferID FROM Sales.SpecialOffer AS SO
WHERE So.DiscountPct <=0.30)

--4) StartDate is 2011-04-01 and EndDate is 2014-01-01 for SpecialOffer
SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.SpecialOfferID IN (SELECT SO.SpecialOfferID FROM Sales.SpecialOffer AS SO
WHERE So.StartDate >='20110401' AND SO.EndDate <'20140101')

--5) Groupped category's average maxQTY is not null
SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.SpecialOfferID IN
(
 SELECT So.SpecialOfferID FROM Sales.SpecialOffer AS SO
 WHERE So.Category IN
 (
  SELECT Category FROM Sales.SpecialOffer AS SO1
  GROUP BY Category
  HAVING AVG(So1.maxQTY) IS NOT NULL))

As you can see from the queries, it is possible to select and execute subquery part of query and get response easily.

Correlated subqueries
This type of subquery has direct dependency on the outer query. So, at least one column from the outer query should participate in the subquery. This will allow us to get “statistical” information related to a given column from the main query.

Here are some use cases on how to use correlated subqueries:
/*
   Retrieve all SpecialOffer information where MinQty is not empty  PER SPECIALOFFERID column
*/
SELECT * FROM Sales.SpecialOffer AS SOD
WHERE SOD.SpecialOfferID IN
(SELECT SO.SpecialOfferId FROM Sales.SpecialOffer AS SO
WHERE SO.SpecialOfferID = SOD.SpecialOfferID AND SO.MinQty IS NOT NULL)

/*
    Show all PurchaseOrderDetail info PER product WHERE listprice is greater than 100
*/
SELECT * FROM Purchasing.PurchaseOrderDetail AS POD
WHERE POD.ProductID IN
(SELECT P.ProductId FROM Production.Product AS P
WHERE P.ProductID = POD.ProductID AND P.ListPrice > 100)

Understanding Subqueries is essential when we need to glean "statistical" information from given or related tables. Self-contained subqueries can be executed in isolated context, meanwhile correlated subqueries depend on the main/outer query.

HostForLIFEASP.NET SQL Server 2021 Hosting

T-SQL provides different ways to work, not just with tables, but also with other data containers, such as XML, JSON, etc. In this article, we will talk about two different ways of XML shredding in T-SQL.

What is “shredding XML”?
XML shredding is a specific term for "transforming” XML content into table representation.

Preparation
First of all, we need some XML data. This can be any data (loaded from the internet, custom defined, etc.).

Also, you can store the XML in your drive, directly insert it into your table, or at least define an XML variable and store it inside.

Loading XML
I stored my XML in drive C:/ and using OPENROWSET, and stored it in the @xmlContent variable.

Shredding using OPENXML
The first way of shredding XML is using the OPENXML command. It needs some additional preparation, but we can load big XML content at one time and do any number of manipulations between the scope. Before “parsing” XML to a table, we must prepare XML for parsing. sp_xml_preparedocument is a stored procedure that helps to load XML content into SQL SERVER’s memory (cache). This procedure also provides a way to access already loaded xml using a user-defined handler. This handler is a “pointer” to the memory where we loaded our XML file. This means that there is no need to use the XML variable after retrieving the handler.

After finishing our operations, we need to clear the cache. For that, we have the sp_xml_removeddocument stored procedure.

Shredding using XQuery
XQuery is also a powerful tool when it comes to shredding XML. It is more flexible and relevant for parsing small XML content. As opposed to OpenXml, XQuery doesn’t require any preparation. So, there is no need to build in any procedures to shred the content. XQuery provides special methods to work with XML . In most case we use nodes() and value() to read data.

Using XQuery and OpenXML, we can “convert” XML to table. In technical literature, we call it “shredding”. OpenXML allows you to work with big XML content, but it requires XML to be loaded into memory entirely. On the other hand, XQuery doesn’t load anything into memory and is faster with working small XML data.

HostForLIFEASP.NET SQL Server 2021 Hosting

We are going to discuss bulk data insertion from multiple tables

    There are many scenarios in which we want to insert a large amount of data from one table to another which has the same schema and all things
    We discussed two ways to insert bulk data manually inside the SQL Table using UNION ALL
    Also, after selecting the data from one table using select query and then inserting that into another by using insert and select query with the help of union all

Let’s start

Step 1
Create a Product Table inside the database
CREATE TABLE DummyProduct (
    ProductID int,
    ProductName varchar(255),
    ProductDescription varchar(255),
    ProductPrice varchar(255),
    ProductStock varchar(255)
);

Step 2
First, we look manual process
select 1, 'Mobile', 'IPhone 12', 80000, 200
UNION ALL
select 2, 'Laptop', 'HP Pavilion 15', 100000, 100
UNION ALL
select 3, 'TV', 'Samsung Smart TV', 35000, 300

Here, you can see we use multiple select queries with union all, you will see the following output after executing all the above query

Step 3
Now, we are going to insert all data in the Product table in only one transaction
INSERT into Product
select 1, 'Mobile', 'IPhone 12', 80000, 200
UNION ALL
select 2, 'Laptop', 'HP Pavilion 15', 100000, 100
UNION ALL
select 3, 'TV', 'Samsung Smart TV', 35000, 300

Here see first we write insert query and below that put all select query using union all and when we execute all this bunch of SQL Query at a time then all the data which are present in the select query is inserted into the product table as shown below

(Note – The number of columns that are present in the select query will be the same as present in the targeted table)

Step 4
Now we are going to insert the Product table records into the new DummyProduct Table with adding new one record as shown below
--Create DummyProduct Table
CREATE TABLE DummyProduct (
    ProductID int,
    ProductName varchar(255),
    ProductDescription varchar(255),
    ProductPrice varchar(255),
    ProductStock varchar(255)
);

--Insert data in bulk
INSERT into DummyProduct
select * from Product
UNION ALL
select 4, 'Keyboard', 'HP Gaming Keyboard', 2000, 400

//select all the record
select * from DummyProduct

Here, you can see we write insert query of DummyProduct and below that put two select query. One takes three records from Product table and the second one takes one record from the static select query, after executing all the above SQL Queries at a time you will see below output as I showed below.

This is all about the bulk insertion of the table using union all.

HostForLIFEASP.NET SQL Server 2021 Hosting

This article demonstrates how we can easily rename various database objects like Tables, Columns, Constraints, Indexes, SP in SQL Server. You may have faced a scenario where sometimes we need to rename database objects to specify correct business significance and meaning to the system on production code. The database objects which were originally designed may not match the current business objects. To solve this problem, you may need to rename existing database objects like table name, column name, store procedure name, etc. The best and easiest way is to use SP_RENAME, a build-in stored procedure to rename these objects.

This approach is recommended because we can run pre-deployment scripts in the environment before deploying these changes.

SP_RENAME takes below arguments.

To demonstrates this, I am creating a table with Primary key, check constraint, non-clustered index and putting some data into this table. We will compare before and after snapshots.

CREATE TABLE OrderInfo
(
  Id INT IDENTITY(1, 1) NOT NULL,
  OrderMode VARCHAR(20) CONSTRAINT [CK_OrderInfo_OrderMode] CHECK (OrderMode IN ('ONLINE','OFFLINE')) NOT NULL,
  OrderName VARCHAR(100) NOT NULL,
  OrderDate DATETIME CONSTRAINT [DF_OrderInfo_OrderDate] DEFAULT (GETDATE()) NOT NULL,
  CONSTRAINT PK_OrderInfo_Id PRIMARY KEY NONCLUSTERED (Id ASC)
)

CREATE NONCLUSTERED INDEX IX_OrderInfo_OrderMode ON dbo.OrderInfo (OrderMode)

INSERT INTO  OrderInfo
VALUES
    ( 'ONLINE', 'Notebook', GETDATE()),
    ( 'ONLINE', 'PC', GETDATE()),
    ( 'OFFLINE', 'Printer', GETDATE())
GO

Before snapshot of table, constraints, and index.

Rename a Table
--Rename table OrderInfo to OrderSummary
EXEC SP_RENAME 'dbo.OrderInfo', 'OrderSummary'

Rename a Column
--Rename column Id to OrderSummaryId
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= 'OrderSummary' AND COLUMN_NAME='Id')
BEGIN
EXEC SP_RENAME 'dbo.OrderSummary.Id', 'OrderSummaryId'
END

Rename an Index
--REANME INDEX
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_OrderInfo_OrderMode' AND OBJECT_ID = OBJECT_ID('dbo.OrderSummary'))
BEGIN
EXEC SP_RENAME 'dbo.OrderSummary.IX_OrderInfo_OrderMode','IX_OrderSummary_OrderMode','INDEX';
END

Rename a Primary Key Constraint
--REANME PRIMARY KEY CONSTRAINT
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME ='PK_OrderInfo_Id')
BEGIN
EXEC SP_RENAME 'dbo.PK_OrderInfo_Id','PK_OrderSummary_OrderSummaryId','OBJECT';
END

Rename a Check Constraint
--REANME CHECK CONSTRAINT
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME ='CK_OrderInfo_OrderMode')
BEGIN
EXEC SP_RENAME 'dbo.CK_OrderInfo_OrderMode','CK_OrderSummary_OrderMode','OBJECT';
END

Rename a Stored Procedure
--RENAME SP
EXEC SP_RENAME 'dbo.spGetOrderInfoByOrderMode' , 'spGetOrderSummaryByOrderMode';

Let’s verify the changes have been made by issuing a SELECT against the table using new table name.

One thing to keep in mind is that when we rename these objects, we need to make changes in dependencies of these objects. For example – if you are renaming a table and that table is being used in multiple SPs then we also to modify those SPs as well. But that is a manual activity to find and fix. This warning represents the same thing.

Hope you find this information useful. Happy Learning!

In this article, we will learn how to remove all reversed number pairs from given table, keep only one (random) in output table if something exists. This SQL problem is a often asked in interview questions, so this have been asked by multiple companies for the data analyst, data science and data engineering interviews. And in this input table for this simplicity we can consider there will be one entry for such kind thing like (1,2) and (2,1) can only be exist once there won't be any such entries like one entry for (1,2) and (2,1) repeated twice or thrice. This is our SQL problem in this article we are going to solve the statement.

Using join operation and where clause
At first we need create a database (here in my case database name is example) then create a table (here in my case i have taken table name is details) now insert some dummy data by using below SQL Query

create database example;
use example
create table details(A int, B int)
insert into details values(1,2);
insert into details values(3,2);
insert into details values(2,4);
insert into details values(2,1);
insert into details values(5,6);
insert into details values(4,2);

now check the table data using below query
select*from details

Output

So first try to solve it using the join operation. so here first let me explain how this is going to work so we can do this self join operation with a condition to change those pairs together (1,2) , (2,1) , (2,4) and (4,2).  We can apply where condition where we can eliminate anyone of it either (1, 2) or (2, 1) for the self a join operation. The on condition which is writing. That is little bit tricky, not very complex to understand and how we can actually change these two pairs together (1, 2) to (2,1) So let's say this is my table T1 and again we are using these self join here. So that table we can name it as a T2. So what we actually need to check in the on condition for the table T1 the column of value B should be equals to the column of value A in T2 table and the second condition part will be the column value of A from table T1 should be equal to the column value of B from the T2 table.

So this would be our join condition. So first let me try to write that join condition for you. Then it will be more clear to you. So here what we're trying to do, we will be doing a left join operation while left join again with this table number pairs and we will name it as a T2 and what would be my join condition. The join condition will be value of B column from T1 table should be equals to value of A from the T2 table. And the second part of the condition will be T1 of A Should be equals to T2 of B or directly you can use the below code:

SELECT  *  FROM details t1
LEFT JOIN details t2
  ON t1.B = t2.A
  AND t1.A = t2.B;

Output

See the below snapshot. Blue highlighted data is coming from the table T1 and Red highlighted data is actually coming from the table T2.

So you can see simply how this pair (1,2) got chained with (2,1)  based on just above mentioned code condition since (3,2) there was nothing as such available. That's why I use left join. Because we will be using these null related values in order to filter these pairs as well because we need it in our output. so this one is clear that how it got joined and when we will come to this pair (2,4) this one reversed pair was (4,2) and now (2,1) Obviously this condition will get satisfied because we're doing a self join, so this pair will find this one as a reversed pair and that's why it is being populated here for (5,6) there was number such reverse pair exist, so that's why these values are null and (4,2) we found (2,4) and now we need to print our output. An in our output, if you remember the values were like this among these two pairs (1,2) and (2,1) . We were picking this single value only and how we were actually picking single value, the value from the column A from table T1 should be less than the value of column A from the table T2. This kind of comparison we can put and this way we will be only able to pick this pair and we will be discarding the repeated values and same thing applies for (2,4) and (4,2)  and we need these records as well. So simply we can put a check for this column right where the T2.A is null. In that case we're simply picking single value. No need to check for any quality related thing. So this is our join part. We need to select the columns which we want in our final output so that we are going to select from T1 table itself T1.B once we are getting the join output then we are applying where clause, first where clause will be that if this value of A  column from T2 is null then straight forward we are going to pick these records. There is no further checking required or let's say this is populated. In that case we need to apply the logic for the value of column A from T1 table should be less than value of. A column from the T2 table. That's how we are going to pick any of one among these two like (1,2) to (2,1). Now let me print this one and here, we can see the output which we were looking for or directly you can use the below code:

SELECT
  t1.A,
  t1.B
FROM details t1
LEFT JOIN details t2
  ON t1.B = t2.A
  AND t1.A = t2.B
WHERE t2.A IS NULL
OR t1.A < t2.A;

Output

HostForLIFEASP.NET SQL Server 2021 Hosting

Before SQL Server 2017 (SQL Server 2014 below), concatenating rows of strings into one column could be done using the STUFF function that combines with FOR XML PATH. However, in my opinion, it's quite messy. In this article, we'll explore SQL's STRING_AAG function and see how we can concatenate rows of strings into one column string using a separator.

The examples provided will work with SQL Server 2017 and later.

Ok, let's get started.

What's SQL's STRING_AAG Function?
    It is an aggregate function that concatenates strings into a single line separated by a separator.
    The separator is not appended to the end of the result string.
    This function was introduced into SQL Server 2017 and later.

Syntax
STRING_AGG(string expression, separator) [order clause]
    The string expression could be any type.
        It can be converted into VARCHAR or NVARCHAR during concatenation.
        Non-string types are converted to NVARCHAR types.
    The separator is used as a separator for the concatenated strings.
        It can be literal or variable. Just remember it doesn't add itself at the end of the result string.
    The order clause is the sort order of the result string using the WITHIN GROUP clause.

Syntax of WITHIN GROUP
WITHIN GROUP (ORDER BY expression [ASC | DESC])
    The expression can be used for sorting results; only one expression is allowed per query.
        The default order is ascending.

Examples
Before we show examples, the AdventureWorks database will be used for the first of our samples.
Using STRING_AGG First Example

To have an overview, the [Sales].[SalesOrderHeader] under the AdventureWorks database does have a [SalesOrderNumber] column.

And we wanted to show the list of different sales-order-number per customer by using the STRING_AGG function.

Ok, let's see the examples below.

SELECT CustomerID as [Customer ID],
       COUNT(CustomerID) as [Number Of Sales Order] ,
       STRING_AGG([SalesOrderNumber], ',') as [Sales Order List]
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
GROUP BY CustomerID

As you can see, with the query that we have we did get the [CustomerID] column as our reference for a particular customer.

Then by knowing the number of its records, we can show the different sales-order numbers per record.

But, of course, using the STRING_AGG.

That's why we can come up with the query above.

Let's see the output below.

Output

Now, for us to appreciate the WITHIN GROUP syntax when using STRING_AGG.

Let's look at the example below.

SELECT CustomerID as [Customer ID],
       COUNT(CustomerID) as [Number Of Sales Order],
       STRING_AGG([SalesOrderNumber], ',')
       WITHIN GROUP (ORDER BY [SalesOrderNumber] DESC)
       as [Sales Order List]
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
GROUP BY  CustomerID

Output

Now, let's try to see the difference between the two outputs.

Output Difference

Using STRING_AGG Second Example
In this section, I will try to give another example. The idea here is to get all the phone numbers of a particular customer; it seems easy, right?
Yap, let's show the code now, and let's create the table structure.

Note: We'll use a local temporary table to avoid tabl structure complexities.

Build the structure first.
-- 1. Let's create the tables needed.
IF OBJECT_ID(N'tempdb..#Customers') IS NOT NULL
BEGIN
    DROP TABLE #Customers
END

CREATE TABLE #Customers(
    Id int,
    FirstName nvarchar(50),
    LastName nvarchar(50)
)

IF OBJECT_ID(N'tempdb..#CustomersPhone') IS NOT NULL
BEGIN
    DROP TABLE #CustomersPhone
END

CREATE TABLE #CustomersPhone(
    PhoneId int,
    UserId int,
    PhoneNumber nvarchar(50)
)

Second, let's put some data on it.
-- 2. Let's put some data on it.

INSERT INTO #Customers VALUES
(1, 'Peter', 'Scott'),
(2, 'Mark', 'Tom')

INSERT INTO #CustomersPhone
VALUES
(1, 1, '+63 895 789 5751'),
(2, 1, '+63 795 689 5752'),
(3, 1, '+63 695 589 5753'),
(1, 2, '+63 915 739 5651'),
(2, 2, '+63 917 649 5552'),
(3, 2, '+63 095 559 5453');

Third, let's create a query that will show the customer's name, the number of phones they have, and their phone list separated by a comma.

-- 3. Let's show the number of phone and phone list a customer have.
SELECT CONCAT(C1.[LastName], ', ', C1.[FirstName]) as [FullName],
       COUNT(C1.Id) as [Total Phone Number],
       STRING_AGG (P1.[PhoneNumber], ',') as [Phone List]
FROM #CustomersPhone P1
INNER JOIN #Customers C1 ON P1.UserId = C1.Id
GROUP BY C1.Id, C1.[LastName], C1.[FirstName]

HostForLIFEASP.NET SQL Server 2021 Hosting

SQL Server 2012 introduced two new string functions: CONCAT and FORMAT. The CONCAT string function concatenates two or more strings into one string. CONCAT takes string arguments as input and concatenates these string inputs into a single string. It requires a minimum of two strings as input, otherwise it raises a compile time error. Here all arguments (inputs) are converted into a string type implicitly. A null value is implicitly converted into an empty string.
 
Syntax
CONCAT ( stringvalue1, stringvalue2 ,…, stringvalueN )
 
Argument / parameter
String Value:  A String value to concatenate with the other.
 
Example
--Example of simple string concatenation
SELECT CONCAT('Hi, ', 'I AM ', 'Peter' )

--Output
-- Hi, I AM Peter
--Example of NULL  string concatenation
SELECT CONCAT('Hello ', NULL, 'World' );

--Output
-- Hello World
--Example of other Data type  concatenation with string

SELECT CONCAT('Date : ', CAST(GETDATE() AS DATE));
--Date : 2013-09-25
SELECT CONCAT(123 + '.' + 45  );

--123.45

If all arguments are null then this function returns a string of type VARCAHR (1). The return type of this function depends on the arguments.
    If an argument is a SQL type NVARCHAR (MAX) or SQL CLR system type then the return type of this function is NVARCHAR (MAX)
    If an argument is VARBINARY (MAX) or VARCHAR (MAX) then the result type is VARCHAR (MAX) and if one of argument is NVARCHAR then the output is NVARCHAR (MAX).
    If an argument is NVARCHAR (<= 4000) than result  type is NVARCHAR (<= 4000)
    All other cases result in a type of VARCHAR (<=8000).
    When the length of the arguments are less than 4000 for NVARCHAR or less than 8000 for VARCHAR, implicit conversions can affect the length of the result type.

Other data types, like INT and FLOAT have different lengths when converted to a string. For example, an INT data type length is 12 when converted to a string, so the result of concatenating two integers has a length of 24.
 
FORMAT
The FORMAT string function returns a string formatted value with the specified format and culture (this is optional). We can use the FORMAT function for locale-aware formatting of a date and time and number values as a string.
 
Syntax
FORMAT (Value, Format , Culture)
 
Arguments / parameters
Value -  value in supported data type to format
Format - NVARCHAR format pattern. This argument must contain a valid .NET framework format string. Composite formatting is not supported. It is either a standard format string or a pattern for custom characters for dates and numeric values.
Culture - NVARCHAR Type specifies a culture.

A culture argument is optional, if we are not providing the culture value then the language of current session is used. The language can be set implicitly or explicitly (using a SET LANGUAGE statement). The culture argument accepts all cultures supported by the .NET Framework. It is not limited to the languages supported by SQL Server. If the culture argument is invalid then the Format string function throws an error.

Please refer to http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo(v=vs.80).aspx to learn more about cultures supported by the .NET Framework.
 
Return Type is NVARCHAR or NULL
A FORMAT string function always returns NULL for an error when the culture is invalid. For the argument value, supported data types are numeric (like TINYINT, SMALLINT, INT, BIGINT, FLOAT, NUMERIC, DECIMAL, SMALLMONEY, MONEY and REAL) and date and time data type (like DATE, TIME, SMALLDATETIME, DATETIME, DATETIME2 and DATETIMEOFFSET). This function will not be remote and it depends on the presence of the Common Language Runtime (CLR).
 
Example
--Example of simple FORMAT string function
DECLARE @mydate DATETIME = '09/25/2013';
SELECT FORMAT ( @mydate, 'd', 'en-US' ) AS 'US Format'
,FORMAT ( @mydate, 'd', 'en-gb' ) AS 'GB Format'
--Output
--US Format         GB Format
----------------  -------------
--09/25/2013         25/09/2013
DECLARE @mydate DATETIME = '09/25/2013';
SELECT FORMAT ( @mydate, 'D', 'en-US' ) AS 'US Format'
,FORMAT ( @mydate, 'D', 'en-gb' ) AS 'GB Format'

--Output
--US Format GB Format
--Wednesday,September 25, 2013      25 September 2013  
--Example of custom formatting string
DECLARE @mydate DATETIME = '09/25/2013';

SELECT FORMAT ( @mydate, 'dd/MM/yyyy', 'en-US' )
--Output
--25/09/2013

SELECT FORMAT(555230655,'###-##-####')
--Output
--555-23-0655
--Example of formatting numeric type
SELECT FORMAT (8875.644 , 'C' ,  'en-US' ) AS 'Currency Format1'
,FORMAT (8875.644 , 'C0',  'en-US' ) AS 'Currency Format2'

Output
-- Currency Format1         Currency Format2      
  $8,875.64                        $8,876
 
These two newly introduced functions are very useful. The CONCAT string function is useful to concatenate two or more values and values of one or more data types. The FORMAT string function gets a formatted value with the specified format and optional culture.

HostForLIFEASP.NET SQL Server 2021 Hosting

In this article, you will learn how to Format Dates in SQL Server.
Here we will use the “FORMAT” function to format a date in SQL Server

Prerequisites

    Install SQL Server
    Install SQL Server Management Studio (SSMS)

For the above prerequisites, you can follow the article How to Install SQL Server and SQL Server Management Studio (SSMS)

Currently, I have installed SQL Server 2019 and SQL Server Management Studio 18.10 (SSMS) on my windows 11 machine.
Built-in function in SQL Server to get the DateTime value in a specific format

By using some built-in function in SQL Server we can get the DateTime value in a specific format.

For example,

GETDATE()
It returns server DateTime in “YYYY-MM-DD HH:mm:ss.fff” format.

SELECT GETDATE() AS [GETDATE()]
Result:-2022-06-09 12:28:37.787

GETUTCDATE()

It returns DateTime in GMT.

SELECT GETUTCDATE() AS [GETDATE()];
Result:-2022-06-09 07:10:54.350

SYSDATETIME()
It returns the server’s DateTime

SELECT SYSDATETIME() AS [GETDATE()];
Result:2022-06-09 12:41:46.8713228

SYSDATETIMEOFFSET()

It returns the server’s DateTime with time zone in which SQL Server instance is running.

SELECT SYSDATETIMEOFFSET() AS [GETDATE()];
Result:2022-06-09 12:42:15.7936382 +05:30

SYSUTCDATETIME()
It returns server DateTime in GMT.

SELECT SYSUTCDATETIME() AS [GETDATE()];
Result:2022-06-09 07:12:54.4664815

CURRENT_TIMESTAMP
It returns current DateTime of the server.

SELECT CURRENT_TIMESTAMP AS [GETDATE()];
Result:2022-06-09 12:43:40.650

After the CONVERT function, SQL Server added a function (FORMAT) to handle date formatting, giving us a new way to format dates in SQL Server.

To format the date and time data types from a date column (Date, DateTime, etc. Data type) in a table or a variant such as GETDATE(), use the FORMAT function.
Date Format with FORMAT Function

We have many ways to format dates as given below
DD/MM/YYYY
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date;
Result:09/06/2022


DD/MM/YYYY, HH:MM:SS
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date;
Result:09/06/2022, 04:56:44

DDDD,MMMM,YYYY
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date;
Result:Thursday, June, 2022

MMM DD YYYY
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date;
Result:Jun 09 2022

MM.DD.YY
SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.09.22


MM-DD-YY
SELECT FORMAT (getdate(), 'MM-dd-yy') as date;
Result:06-09-22

HH:MM:SS TT
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date;
Result:05:17:37 PM

MM/DD/YYYY (Standard: USA)
SELECT FORMAT (getdate(), 'd','us') as date;
Result:06/09/2022

YYYY-MM-DD HH:MM:SS TT
SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss tt') as date;
Result:2022-06-09 05:18:55 PM

YYYY.MM.DD HH:MM:SS T
SELECT FORMAT (getdate(), 'yyyy.MM.dd hh:mm:ss t') as date;
Result:2022.06.09 05:19:53 P

DDDD,MMM,YYYY in Spanish
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy','es-es') as date;
Result:jueves, junio, 2022

DDDD DD, MMMM,YYYY in Japanese
SELECT FORMAT (getdate(), 'dddd dd, MMMM, yyyy','ja-jp') as date;
Result:木曜日 09, 6月, 2022

Date Format with Culture
We can get regional formatting by using the culture option as shown below:

English-USA
SELECT FORMAT (getdate(), 'd', 'en-US') as date;
Result:6/10/2022

French-France
SELECT FORMAT (getdate(), 'd', 'fr-FR') as date;
Result:10/06/2022

French - Belgium
SELECT FORMAT (getdate(), 'd', 'fr-BE') as date;
Result:10-06-22

French - Canada
SELECT FORMAT (getdate(), 'd', 'fr-CA') as date;
Result:2022-06-10

Danish - Denmark
SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.10.22

Dari - Afghanistan
SELECT FORMAT (getdate(), 'd', 'prs-AF') as date;
Result:1401/3/20


Simplified Chinese
SELECT FORMAT (getdate(), 'd', 'zh-CN') as date;
Result:2022/6/10

Divehi - Maldives
SELECT FORMAT (getdate(), 'd', 'dv-MV') as date;
Result:10/06/22

Bosnian Latin
SELECT FORMAT (getdate(), 'd', 'bs-Latn-BA') as date;
Result:10. 6. 2022.

isiXhosa / Xhosa - South Africa
SELECT FORMAT (getdate(), 'd', 'xh-ZA') as date;
Result:2022-06-10

Hungarian - Hungary
SELECT FORMAT (getdate(), 'd', 'hu-HU') as date;
Result:2022. 06. 10.

Spanish - Bolivia
SELECT FORMAT (getdate(), 'd', 'es-bo') as date;
Result:10/6/2022

Here is a list of all CultureInfo codes along with country names and language.

As you saw above, we have used a lot of options for date and time formatting, which are detailed below,

• hh - this is the hour from 01-12
• HH - this is the hour from 00-23
• mm - this is the minute from 00-59
• ss - this is the second from 00-59
• dd - this is day of month from 01-31
• dddd - this is the day spelled out
• MM - this is the month number from 01-12
• MMM - month name abbreviated
• MMMM - this is the month spelled out
• yy - this is the year with two digits
• yyyy - this is the year with four digits
• tt - this shows either AM or PM
• d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
• us - this shows the date using the US culture which is MM/DD/YYYY

HostForLIFEASP.NET SQL Server 2021 Hosting