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