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

大部分的日期格式字串無區分大小寫,但若指定MONTH格式並轉換成英文月份名稱時,日期格式將區分大小寫。
其中MONTHDAY兩種轉換格式中特別加註「空白填充為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_langAmerican,以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)

日期型態資料以指定格式轉換為字元型態。先前版本可使用CASTCONVERT函數,為避免資料格式不符而發生異常,需額外增加檢查語法,2012起提供TRY_CASTTRY_CONVERT函數以解決此問題,當格式不符時則傳回NULL
MSSQL2012另提供可指定文化/語系(culture)特性,使用上相當直覺且彈性的FORMAT函數,此函數可使用標準格式字串 (例如: "D""C")自訂字元模式 (: "yyyy-MM-dd""#,#"),可將日期與數值型態以.NET格式產生格式化字串;而反向轉換則提供PARSE轉換函數,可依指定文化/語系(culture)特性將字元型態轉換為日期/時間或數字型態,但其他型態轉換仍需使用CONVERTCAST等類型函數,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或其他為解決異常所提供的新函數,為提供轉換上對應比較,將以併列呈現方式表列,另外,由於CONVERTCAST對應則提供TRY_CONVERTTRY_CAST函數其使用方法、參數均相同,範例上仍以舊有函數為例

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

詳細說明及使用方法請參閱『[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
以上SQLDATEDIFF(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)大部分是不分大小寫。由上表,MSSQLFORMATORACLETO_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


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


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


沒有留言:

張貼留言