[MSSQL] FORMAT을 사용한 날짜 및 시간 형식 지정

출처 : riptutorial.com/sql-server/example/8084/date---time-formatting-using-format

SQL Server 2012 부터 사용할 수 있다고 합니다.

SELECT FORMAT(GETDATE(), 'yyyyMMddHHmmss')

결과 : 20201201181615

---

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

yyyy	2016
yy	16
MMMM	September
MM	09
M	9
dddd	Monday
ddd	Mon
dd	05
d	5
HH	00
H	0
hh	12
h	12
mm	01
m	1
ss	02
s	2
tt	AM
t	A
fff	333
ff	33
f	3

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

D	Monday, September 05, 2016
d	9/5/2016
F	Monday, September 05, 2016 12:01:02 AM
f	Monday, September 05, 2016 12:01 AM
G	9/5/2016 12:01:02 AM
g	9/5/2016 12:01 AM
M	September 05
O	2016-09-05T00:01:02.3330000
R	Mon, 05 Sep 2016 00:01:02 GMT
s	2016-09-05T00:01:02
T	12:01:02 AM
t	12:01 AM
U	Monday, September 05, 2016 4:01:02 AM
u	2016-09-05 00:01:02Z
Y	September, 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')