2013年9月9日 星期一

[2012] 格式化函數FORMAT介紹

{最後修訂: [2020-06-20]參數重新整理}
格式化函數FORMAT可將日期/時間數值以指定格式/地區設定轉換為格式字串。語法如下:
FORMAT (value, format [, culture ] )
value(必要參數)
欲格式化之運算式。

format(必要參數)
format 引數應為有效的 .NET Framework 格式字串
支援標準格式字串 (例如 "C" "D")日期與數值自訂字元模式 ( "yyyy-MM-dd"),但不支援兩者複合格式。

culture :(選擇性參數)
指定文化特性格式。
如未提供 culture 引數,則會使用目前工作階段的語言。可使用SET LANGUAGE陳述式隱含或明確加以設定。

FORMAT格式化函數可使用標準格式字串 (例如: "D""C")自訂字元模式 (: "yyyy-MM-dd""###,###")日期與數值型態以.NET格式產生格式化字串。建議可以參考C#書籍或官方網址(如下)
格式化字串
網址
日期
標準
自訂
數值
標準
自訂

(1).日期格式化
日期型態格式可分成標準及自訂格式兩類,可簡單以格式字串字元數來區分,標準格式使用一個字元,而任何包含一個以上字元(包括空白字元)將被視為自訂格式字串。

標準日期格式字串
以下整理出比較常見、誤用或實用性較高者,以SQL產生en-us(美國)zh-tw(台灣)兩種結果,並以2020-06-18 22:58:17.158為例,比較其差異。
 
fmt
Descr
default
zh-tw
1
d
簡短日期
6/18/2020
2020/6/18
2
D
完整日期
Thursday, June 18, 2020
2020618
3
y
年月
Jun-20
20206
4
Y
年月
Jun-20
20206
5
m
月日
2020/6/18
618
6
M
月日
2020618
618
7
s
可排序
2020-06-18T22:58:17
2020-06-18T22:58:17
SELECT fmt, Descr
, FORMAT(CAST('2020-06-18 22:58:17.158' as datetime), fmt)          "default"
, FORMAT(CAST('2020-06-18 22:58:17.158' as datetime), fmt, 'zh-tw') "zh-tw"
FROM (VALUES ('d', N'簡短日期'), ('D', N'完整日期')
            , ('y', N'年月'),    ('Y', N'年月')
            , ('m', N'月日'),    ('M', N'月日')   
           , ('s', N'可排序 ')  
     ) F (fmt, Descr)

自定格式化字串
日期格式化通常用自訂格式化方式,常見格式化字串整理如下,並以2020-06-18 22:58:17.158為例。

日期格式化字串

2020-06-18 22:58:17.158為例
說明
y
年月(標準日期格式)
yy
(2)
yyyy
(4)
M
月日(標準日期格式)
MM
( 01~12)
MMM
(縮寫)
MMMM
(完整)
d
(標準日期格式)
dd
(01~31)
ddd
週幾 (週一至日) (縮寫)
dddd
星期幾 (星期一至日) (完整)
hh
小時 ( 01~12, 12小時制)
HH
小時 ( 00~23, 24小時制)
mm
分鐘 ( 00~59)
ss
(00-59)
tt
.下午 (AM or PM)
注意: /分鐘,小時(1224)區分大小寫。

fmt
default
zh-tw
y
June 2020
20206
yy
20
20
yyyy
2020
2020
M
June 18
618
MM
6
6
MMM
Jun
六月
MMMM
June
六月
d
6/18/2020
2020/6/18
dd
18
18
dddd
Thursday
星期四
d
6/18/2020
2020/6/18
hh
10
10
HH
22
22
mm
58
58
ss
17
17
tt
PM
下午
: fmt字串可自行組合

SQL
SELECT fmt
, FORMAT(CAST('2020-06-18 22:58:17.158' as datetime), fmt)          "default"
, FORMAT(CAST('2020-06-18 22:58:17.158' as datetime), fmt, 'zh-tw') "zh-tw"
FROM (VALUES ('y') , ('yy'), ('yyyy')
           , ('MM'), ('MMM'), ('MMMM')
           , ('dd'), ('dddd'), ('d')
           , ('hh'), ('HH')
           , ('mm'), ('ss')
           , ('tt')
     ) F (fmt)

可將上述格式字串進行組合,包含日期、時間之連結符號亦可彈性選用,也可搭配特定日期方式達成月初(yyyy-MM-01)、年初(yyyy-01-01)、年底(yyyy-12-31)等,另外也可使用標準格式字串(dD),結果如下:
功能
SQL / 結果
SQL
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd')        "yyyy-MM-dd"
  , FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')  "yyyy-MM-dd HH:mm:ss"
  , FORMAT(GETDATE(), 'HH')                     "24H"
  , FORMAT(GETDATE(), 'hh')                     "12h"
  , FORMAT(GETDATE(), 'yyyy-MM-01')            "1stDayofMonth"
  , FORMAT(GETDATE(), 'd', 'en-US' )           "US(fmt)"
  , FORMAT(GETDATE(), 'd', 'zh-tw' )           "TW(fmt)"
  , FORMAT(GETDATE(), 'D', 'zh-tw' )           "Chinese(ZH)"
結果
建議2012以上版本改用FORMAT函數,可直接套用.NET中日期格式化字串直接進行轉換,如月初、年初、年底等常用日期均可簡便產生,以供報表、查詢起訖時間計算上使用。

(2).數值格式化
數值格式化部分支援標準格式字串 (例如 "C" "D")及自訂字元模式 ( "#,0.##"),但不支援兩者複合格式。

標準數值格式字串
標準數值格式字串用以格式化一般數字類型,標準數值格式字串是採用Axx格式,xx是「精確度規範」選擇性參數定義為099整數,將影響結果呈現之位數,當應用於控制小數點後之位數時,則會以四捨五入方式進行捨位運算。

自定格式化字串
自定格式模式時,小數位部分之位數依定義格式而決定並進行四捨五入,建議具小數位者其個位數格式應設應為0,當數值絕對值為小於1時,個位數部分可自動以0填補。就使用彈性而言,建議使用自定格式模式。
格式
說明
範例
C
N

C: 貨幣; N:數值
(兩者差別在於貨幣符號)
預設小數位2
SELECT FORMAT(123456.789, 'C')
     , FORMAT(123456.789, 'C4', 'en-us ')
------------- ---------------
NT$123,456.79 $123,456.7890
E

指數 (科學記號)
預設小數位6
SELECT FORMAT(1234.56789, 'E')
     , FORMAT(1234.56789, 'E4')
------------- ----------
1.234568E+003 1.2346E+003
P

P: 百分比
%: 乘以100 並加上百分比符號
‰:乘以1000 並加上千分比符號
SELECT FORMAT(0.56789, 'P') 
     , FORMAT(0.56789, 'P2')
     , FORMAT(0.56789, '0.00%')
     , FORMAT(0.56789, '0.00‰')
------   ------   -------  ------
56.79%   56.79%   56.79 %  567.89‰
0

0零值預留位置

SELECT FORMAT(1234.5678,  '00000')
     , FORMAT(0.5678,  '0.00')
------   ---------
01235    0.57
#
#數字預留位置
僅有小數時則『0』將被剔除

SELECT FORMAT(1234.5678,  '#####')
     , FORMAT(0.5678,  '#.##')
-------  ---------
1235   .57
,
千分位
SELECT FORMAT(1234.5678,  '#,0')
     , FORMAT(1234.5678,  '#,0.#')
     , FORMAT(0.5678,     '#,0.#')
------   -------  ---
1,235    1,234.6  0.6
;
區段分隔符號
定義正數、負數和零值的區段
SELECT FORMAT( 1, '#;(#);-')
     , FORMAT(-1, '#;(#);-')
     , FORMAT( 0, '#;(#);-')
---  ---  ---
1    (1)  -
\
逸出字元

如欲輸出格式字串,如"#""0""."",""%" "‰" 符號時,可於前方加逸出字元即可。

沒有留言:

張貼留言