Date and Time formatting using Format() function in SQL Server

Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

Now Let's see the below example. I have demonstrated all the output for the given argument.

Example:

DECLARE @Date DATETIME = Getdate()

SELECT FORMAT(@Date, N'dddd, MMMM dd, yyyy hh:mm:ss tt')

Output:

Sunday, May 30, 2021 01:28:44 PM

Argument	Output
yyyy	2021
yy	21
MMMM	May
MM	05
M	5
dddd	Sunday
ddd	Sun
dd	30
d	30
HH	13
H	13
hh	01
h	1
mm	28
m	28
ss	44
s	44
tt	PM
t	P
fff	597
ff	59
f	5

You can also supply a single argument to the FORMAT() function to generate a pre-formatted output.

DECLARE @Date DATETIME = GETDATE()

SELECT FORMAT(@Date, N'U')

Argument	Output
D	Sunday, May 30, 2021
d	5/30/2021
F	Sunday, May 30, 2021 1:44:12 PM
f	Sunday, May 30, 2021 1:44 PM
G	5/30/2021 1:44:12 PM
g	5/30/2021 1:44 PM
M	May 30
O	2021-05-30T13:44:12.4870000
R	Sun, 30 May 2021 13:44:12 GMT
s	2021-05-30T13:44:12
T	1:44:12 PM
t	1:44 PM
U	Sunday, May 30, 2021 8:14:12 AM
u	2021-05-30 13:44:12Z
Y	May 2021

Note: The above list is using the en-US culture. A different culture can be specified for the FORMAT() via the third parameter.

DECLARE @Date DATETIME = '2016-09-05 00:01:02.333'

SELECT FORMAT(@Date, N'U', 'zh-cn')

Output:

2021年5月30日 10:10:53

Date and Time formatting using CONVERT() Function in SQL Server

You can use the CONVERT function to cast a datetime datatype to a formatted string.

You can also use some built-in codes to convert into a specific format. Here are the options built into SQL Server.

SQL Code:

SELECT GETDATE() AS [Result] --2021-05-29 18:50:18.737

DECLARE @convert_code INT = 100 

SELECT CONVERT(VARCHAR(30), GETDATE(), @convert_code) AS [Result]

Output:

@convert_code	Result
100	May 29 2021  6:50PM
101	05/29/2021
102	2021.05.29
103	29/05/2021
104	29.05.2021
105	29-05-2021
106	29 May 2021
107	May 29, 2021
108	18:50:18
109	May 29 2021  6:50:18:737PM
110	05-29-2021
111	2021/05/29
112	20210529
113	29 May 2021 18:50:18:737
114	18:50:18:737
120	2021-05-29 18:50:18
121	2021-05-29 18:50:18.737
126	2021-05-29T18:50:18.737
127	2021-05-29T18:50:18.737
130	18 ???? 1442  6:50:18:737PM
131	18/10/1442  6:50:18:737PM

SQL Server Select into statement with example

The SELECT INTO statement creates a new table and inserts rows from the query into it.

If you want to copy the partial data from the source table, you use the WHERE clause to specify which rows to copy. Similarly, you can specify which columns from the the source table to copy to the destination table by specifying them in the select list.

Note that SELECT INTO statement does not copy constraints such as primary key and indexes from the source table to the destination table.

Syntax:

SELECT Column1, Column2, Column3
INTO MyNewTable
FROM MySourceTable;

Let's see with below examples:

• Create a backup copy of Customers:

SELECT * INTO Customers2017
FROM Customers;

• Copy records from one database to another.

SELECT * INTO Rohit.dbo.PurchaseOrderDetail  
FROM AdventureWorks2017.purchasing.PurchaseOrderDetail

• Copy only a few columns into a new table:

SELECT CustomerName, ContactName INTO Customers2017
FROM Customers;

• Copies only the US customers into a new table:

SELECT * INTO CustomersUS
FROM Customers
WHERE Country = 'US';

SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data.

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

PIVOT and UNPIVOT in SQL Server

There are several ways to transpose a dataset from rows to columns and columns to rows. SQL Pivot is one of the techniques that allows transposing of rows to columns and performs possible aggregations along the way. SQL PIVOT and SQL UNPIVOT relational operators transpose a table-valued two-dimensional data into another form of data. 

SQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. SQL UNPIVOT performs the opposite operation of SQL PIVOT by transforming the columns of a table-valued expression into column values.

PIVOT relational operator converts data from row level to column level.

UNPIVOT relational operator is reverse process of PIVOT relational operator. UNPIVOT relational operator convert data from column level to row level.

Let's see the below example to understand.

Example:

PIVOT

Below is a simple example which shows average item's price of each item per weekday.

First, suppose we have a table which keeps daily records of all items' prices.

CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT);

insert values into the table:

INSERT INTO tbl_stock VALUES
('Item1', 'Mon', 110), ('Item2', 'Mon', 230), ('Item3', 'Mon', 150),
('Item1', 'Tue', 115), ('Item2', 'Tue', 231), ('Item3', 'Tue', 162),
('Item1', 'Wed', 110), ('Item2', 'Wed', 240), ('Item3', 'Wed', 162),
('Item1', 'Thu', 109), ('Item2', 'Thu', 228), ('Item3', 'Thu', 145),
('Item1', 'Fri', 120), ('Item2', 'Fri', 210), ('Item3', 'Fri', 125),
('Item1', 'Mon', 122), ('Item2', 'Mon', 225), ('Item3', 'Mon', 140),
('Item1', 'Tue', 110), ('Item2', 'Tue', 235), ('Item3', 'Tue', 154),
('Item1', 'Wed', 125), ('Item2', 'Wed', 220), ('Item3', 'Wed', 142);

In order to perform aggregation which is to find the average price per item for each week day, we are going to use the relational operator PIVOT to rotate the column weekday of table-valued expression into aggregated row values as below:

SELECT * FROM tbl_stock
PIVOT (
AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt;

Output:

item	Mon	Tue	Wed	Thu	Fri
Item1	116	112	117	109	120
Item2	227	233	230	228	210
Item3	145	158	152	145	125

UNPIVOT

Lastly, in order to perform the reverse operation of PIVOT, we can use the relational operator UNPIVOT to rotate

columns into rows as below:

SELECT * FROM tbl_stock
PIVOT (
AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt
UNPIVOT (
price FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) unpvt;

Output:

item	price	weekday
Item1	116	Mon
Item1	112	Tue
Item1	117	Wed
Item1	109	Thu
Item1	120	Fri
Item2	227	Mon
Item2	233	Tue
Item2	230	Wed
Item2	228	Thu
Item2	210	Fri
Item3	145	Mon
Item3	158	Tue
Item3	152	Wed
Item3	145	Thu
Item3	125	Fri