Microsoft SQL Server Date & Time Formatting using FORMAT


Example

SQL Server 2012

You can utilize the new function: FORMAT().

Using this you can transform your DATETIME fields to your own custom VARCHAR format.

Example

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

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

Monday, September 05, 2016 12:01:02 AM

Arguments

Given the DATETIME being formatted is 2016-09-05 00:01:02.333, the following chart shows what their output would be for the provided argument.

ArgumentOutput
yyyy2016
yy16
MMMMSeptember
MM09
M9
ddddMonday
dddMon
dd05
d5
HH00
H0
hh12
h12
mm01
m1
ss02
s2
ttAM
tA
fff333
ff33
f3

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

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

SELECT FORMAT(@Date, N'U')

Monday, September 05, 2016 4:01:02 AM

Single ArgumentOutput
DMonday, September 05, 2016
d9/5/2016
FMonday, September 05, 2016 12:01:02 AM
fMonday, September 05, 2016 12:01 AM
G9/5/2016 12:01:02 AM
g9/5/2016 12:01 AM
MSeptember 05
O2016-09-05T00:01:02.3330000
RMon, 05 Sep 2016 00:01:02 GMT
s2016-09-05T00:01:02
T12:01:02 AM
t12:01 AM
UMonday, September 05, 2016 4:01:02 AM
u2016-09-05 00:01:02Z
YSeptember, 2016

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')

2016年9月5日 4:01:02