2013年9月28日 星期六

格式轉換函數(日期/文字)

將日期型態資料以指定格式轉換為字元型態。

ORACLE
ORACLE提供TO_CHAR函數，可將日期型態（Date）或數值型態（Number）根據格式(format)定義轉換成格式化字串，此函數支援格式眾多且使用簡便。TO_CHAR函數語法如下：
TO_CHAR(date [, 'format' [, nls_language]])

TO_CHAR函數可用的格式眾多，依轉換型態區分成數值轉換格式（NUMBER FORMATS）及日期轉換格式（DATE FORMATS）兩類，僅節錄日期轉換格式中常用部分，如下表：
fmt
說明
PART
PART(ZH-TW)
HH12
一天的小時數（01-12）
10
10
HH24
一天的小時數（00-23）
22
22
MI
分鐘（00-59）
58
58
SS
秒（00-59）
17
17
SSSS
午夜後的秒（0-86399）
1717
1717
YYYY
年（完整的四位數西元年）
2020
2020
YYY
年的後三位
20
20
YY
年的後兩位
20
20
Y
年的最後一位
0
0
MONTH
全長、大寫月份（空白填充為9字元）
JUNE
6月
MON
大寫縮寫月份（3字元）
JUN
6月
MM
月份號（01-12）
6
6
DAY
全長、大寫日期（空白填充為9字元）
THURSDAY
星期四
DY
縮寫大寫日期（3字元）
THU
星期四
DDD
一年中的日數（001-366）。取得自年初起算的天數
170
170
DD
一個月中的日數（01-31）
18
18
D
一週裡的日數（1-7；週日=1）
5
5
W
一個月中的週數（1-5）（第一週從該月第一天開始）
3
3
WW
一年中的週數（1-53）（第一週從該年的第一天開始）
25
25
IW
ISO一年中的週數（第一個星期四在第一週裡）
25
25
Q
季
2
2
FF
毫秒。需為TIMESTAMP型態才提供
158000000
158000000
資料來源: https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm#OLADM780

大部分的日期格式字串無區分大小寫，但若指定MONTH格式並轉換成英文月份名稱時，日期格式將區分大小寫。
其中MONTH及DAY兩種轉換格式中特別加註「空白填充為9字元」，因英文的九月（September）字元長度為9，其他月份名稱不足9位將以空白填充，Fm（填充模式）參數用以控制TO_CHAR函數傳回值中對填補空白和前導0的抑制。
SELECT column_value Fmt
, TO_CHAR(TO_TIMESTAMP('2020-06-18 22:58:17.158'
                         , 'yyyy-mm-dd hh24:mi:ss.ff')
                    , column_value
                    , 'NLS_DATE_LANGUAGE=American' 
                    ) Part
, TO_CHAR(TO_TIMESTAMP('2020-06-18 22:58:17.158'
                         , 'yyyy-mm-dd hh24:mi:ss.ff')
                     , column_value
                     , 'NLS_DATE_LANGUAGE=''TRADITIONAL CHINESE''' 
                   ) "Part(ZH-TW)"
FROM TABLE(SYS.DBMS_DEBUG_VC2COLL(
'HH12','HH24'
,'MI'
,'SS','SSSS'
,'YYYY','YYY','YY','Y'
,'MONTH','MON','MM'
,'DAY','DY','DDD','DD','D'
,'W','WW','IW','Q'
,'FF'
))
註: 由於[TRADITIONAL CHINESE]兩字中間具有空白，應此使用兩個單引號包夾。
以下將指定nls_lang為American，以6月（June）為例，說明Fm使用前後及Month大小寫應用上差異。測試SQL及結果：
SELECT TO_CHAR(DT, 'YYYY-MM') YrMs
, TO_CHAR(DT, 'Month',   'NLS_DATE_LANGUAGE=American')     "Month"
, TO_CHAR(DT, 'FmMonth', ' NLS_DATE_LANGUAGE =American') "FmMonth"
FROM
     (
     SELECT DATE'2020-06-01' DT
     FROM DUAL
     )
------      ----------  ------
2020-06     June…..   June
未使用fm時，則June字尾後方具有五個空白字元填補成9碼，使用fm後則空白字元被剔除；另外當使用全大寫MONTH時，亦得到全大寫英文月份(JUNE)；但改為字首大寫時，則月份亦同樣變更為僅字首大寫(June)。

日期型態資料以指定格式轉換為字元型態。先前版本可使用CAST、CONVERT函數，為避免資料格式不符而發生異常，需額外增加檢查語法，2012起提供TRY_CAST及TRY_CONVERT函數以解決此問題，當格式不符時則傳回NULL。
MSSQL2012另提供可指定文化/語系(culture)特性，使用上相當直覺且彈性的FORMAT函數，此函數可使用標準格式字串 (例如: "D"、"C")或自訂字元模式 (如: "yyyy-MM-dd"、"#,#")，可將日期與數值型態以.NET格式產生格式化字串；而反向轉換則提供PARSE轉換函數，可依指定文化/語系(culture)特性將字元型態轉換為日期/時間或數字型態，但其他型態轉換仍需使用CONVERT及CAST等類型函數，PARSE函數與其最大差別在於轉換時可指定文化/語系特性進行轉換。語法整理如下：
功能/說明
語法
日期轉換為字元
(D/C, 雙向)
CAST(expr AS datatype [(len)])
TRY_CAST (expr AS data_type [ (len) ] )
日期依格式編碼轉換為字元
(D/C, 雙向)
CONVERT( datatype [(len)] , expr [ , style ] )
TRY_CONVERT( data_type [ ( len ) ], expr [, style ] )
日期/數字依格式字串轉換為字元(D2C, 可指定culture)
FORMAT (date, format [, culture ] )
字元轉換為日期/數字
(C2D, 可指定culture)
PARSE(<str> AS data_type [USING culture ])
TRY_PARSE(<str> AS data_type [USING culture ])

首先建議已升級2012版本以上使用者，嘗試導入FORMAT或其他為解決異常所提供的新函數，為提供轉換上對應比較，將以併列呈現方式表列，另外，由於CONVERT、CAST對應則提供TRY_CONVERT及TRY_CAST函數其使用方法、參數均相同，範例上仍以舊有函數為例。

1.  FORMAT函數
依轉換格式字串定義，將日期/數值型態資料轉換成指定格式字串，語法如下:
FORMAT (date, format [, culture ] )
date：（必要參數）
欲格式化之運算式。
format：（必要參數）
應為有效的.NET Framework 格式字串，支援標準格式字串 (例如 "C" 或 "D")及日期與數值自訂字元模式(如"yyyy-MM-dd")，但不支援兩者複合格式。
culture :（選擇性參數）
指定文化(culture)特性格式。
如未提供culture引數，則會使用目前工作階段的語言。可使用SET LANGUAGE陳述式隱含或明確加以設定。
資料來源：http://technet.microsoft.com/zh-tw/library/hh213505.aspx

詳細說明及使用方法請參閱『[2012]格式化函數FORMAT介紹』。

2.  CAST函數
CAST函數可將日期/數字或日期/字串轉與日期型態互相轉換，TRY_CAST亦同，語法如下：
CAST(expr AS datatype [ (len ) ])

測試SQL及結果：
SELECT CAST('20200623' AS datetime)
, CAST('2020-06-23' AS datetime)
, CAST('2020/06/23' AS datetime)
, CAST(44003 AS datetime)
, DATEDIFF(DD, 0, '2020-06-23') –44003

-------------------    -------------------    -------------------    -------------------    -----
2020-06-23 00:00:00    2020-06-23 00:00:00    2020-06-23 00:00:00    2020-06-23 00:00:00    44003
以上SQL中DATEDIFF(DD, 0, '2020-06-23')是取得與1900-01-01所差異之天數可得44003（整數），範例中再用CAST函數將44003轉換回日期型態可得2020-06-23(日期)，後續將應用此概念於取得月初日期、季初日期或年初日期等。

3.  CONVERT函數
CAST函數無法指定轉換後格式，指定特定格式的需求難以符合，CONVERT函數則可指定其特定格式編碼(style)轉換成文字型態，語法如下:
CONVERT( datatype [ ( len ) ] , expr [ , style ] )
datatype: 指定函數轉換後的資料型態，可指定其長度。
expr: 需要轉換的運算式(欄位/資料)。
style: 設定日期轉換所呈現的格式。常用格式整理如下表。

由於格式編碼並非十分直覺，如已採用MSSQL2012以上版本，則可考慮採用FORMAT函數，以下整理出常用格式編碼，並一併列出對應之FORMAT函數及ORACLE格式字串。
格式編碼
輸出格式
FORMAT格式字串
ORACLE格式字串
101
MM/dd/yyyy
MM/dd/yyyy
MM/DD/YYYY
102
yyyy.MM.dd
yyyy.MM.dd
YYYY.MM.DD
103
dd/MM/yyyy
dd/MM/yyyy
DD/MM/YYYY
104
dd.MM.yyyy
dd.MM.yyyy
DD.MM.YYYY
105
dd-MM-yyyy
dd-MM-yyyy
DD-MM-YYYY
107
Mon dd, yyyy
MMM dd, yyyy
MON DD, YYYY
108
HH:mi:ss (24h)
HH:mm:ss
HH24:MI:SS
110
MM-dd-yyyy
MM-dd-yyyy
MM-DD-YYYY
111
yyyy/MM/dd
yyyy/MM/dd
YYYY/MM/DD
112
yyyyMMdd
yyyyMMdd
YYYYMMDD
114
HH:mi:ss:mmm (24h)
HH:mm:ss:fff
HH24:MI:SS:FF
120
yyyy-MM-dd HH:mi:ss (24h)
yyyy-MM-dd HH:mm:ss
YYYY-MM-DD HH24:MI:SS
註：在114格式中將產生毫秒，ORACLE需採用TIMESTAMP型態才支援。
ORACLE格式字串(fmt)大部分是不分大小寫。由上表，MSSQL的FORMAT與ORACLE的TO_CHAR相當類似。

以下SQL是整理出格式編碼與FORMAT函數的格式字串兩者對應關係，以及資料驗證。
SELECT code
      , fmt
      , FORMAT(DT, fmt)
      , CASE WHEN CONVERT(VARCHAR(20), DT, code)  = FORMAT(DT, fmt) THEN '' ELSE 'V' END Diff
FROM
    (
    SELECT CAST('2020-06-18 22:58:17.158' as datetime) DT
    ) A  
    CROSS APPLY
    (
     VALUES (101, 'MM/dd/yyyy')
                , (102, 'yyyy.MM.dd')
                , (103, 'dd/MM/yyyy')
                , (104, 'dd.MM.yyyy')
                , (105, 'dd-MM-yyyy')
                , (107, 'MMM dd, yyyy')
                , (108, 'HH:mm:ss')
                , (110, 'MM-dd-yyyy')
                , (111, 'yyyy/MM/dd')
                , (112, 'yyyyMMdd')
                , (114, 'HH:mm:ss:fff')
                , (120, 'yyyy-MM-dd HH:mm:ss')
   ) D (code, fmt)


4.  PARSE函數
字元轉換為日期/數字，語法如下
PARSE(str AS data_type [USING culture ])
datatype: 指定函數轉換後的資料型態，可指定其長度。
str：（必要參數）
欲剖析轉換日期字串。
data_type：（必要參數）
轉換結果之資料型態，在此應為日期型態。
culture :（選擇性參數）
指定文化(culture)特性格式。
如未提供 culture 引數，將使用目前工作階段語言。可使用SET LANGUAGE 陳述式隱含或明確加以設定。另外culture接受.NET Framework 所支援的任何文化特性(Culture)，並不限於MSSQL明確支援的語言。

PARSE函數會依文化(culture)特性設定將字元轉轉換為日期型態 如不符合格式則引發錯誤，如改採TRY_PARSE函數則傳回NULL。

-- (1). PARSE
SELECT PARSE('29/02/2020' AS datetime
               USING 'en-GB')--成功
     --, PARSE('29/02/2020' AS datetime)--錯誤
    
--(2). TRY_PARSE    
SELECT TRY_PARSE('29/02/2020' AS datetime USING 'en-GB') --成功
     , TRY_PARSE('29/02/2020' AS datetime)               --NULL
 
 --(3).TRY_CONVERT  
SELECT TRY_CONVERT(datetime, '02/29/2020') --成功
     , TRY_CONVERT(datetime, '29/02/2020') --NULL

--(4).TRY_CAST
SELECT TRY_CAST('02/29/2020' AS datetime) --成功
     , TRY_CAST('29/02/2020' AS datetime) --NULL


MSSQL的CONVERT及CAST函數為雙向函數，即日期/文字可進行互轉，而FORMAT函數也是單向函數，可將日期型態資料轉換為格式字串，使用方法請參閱『[2012] 格式化函數FORMAT介紹』，而PARSE函數則可將字元型態資料轉回日期型態。


對於ORACLE的TO_CHAR函數可將日期型態轉換為文字型態，字元型態轉回日期型態則需使用TO_DATE。TO_DATE函數可依照所指定的日期格式，將日期字串資料轉換為日期型態資料，使用方法與TO_CHAR雷同，語法如下：
TO_DATE(date [, 'format' [, nls_language]])
ORACLE對於日期與文字之間的轉換可使用TO_CHAR及TO_DATE兩個函數進行轉換，如下圖所示：


於

沒有留言:

張貼留言