//--------------------------------------------------------
// 사용 방법 예문
//--------------------------------------------------------
SELECT
FORMAT(cast('2020-12-21 11:22:00:123' as datetime), 'yyyyMMddHHmmssffff') 'yyyyMMddHHmmssffff',
FORMAT(cast('2020-12-21 11:22:00' as datetime), 'yyyyMMddHHmmss') 'yyyyMMddHHmmss',
FORMAT(cast('2020-12-21 11:22:00' as datetime), 'yyyy-MM-dd HH:mm:ss') 'yyyy-MM-dd HH:mm:ss',
FORMAT(cast('2020-12-21 11:22:00' as datetime), 'HH:mm') 'HH:mm'
declare @NowDate varchar(8)
set @NowDate = convert(varchar(8), getdate(), 112)
출처 : https://riptutorial.com/sql-server/example/8084/date---time-formatting-using-format
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.
Argument | Output |
---|---|
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 Argument | Output |
---|---|
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')