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