將日期型態資料以指定格式轉換為字元型態。
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格式並轉換成英文月份名稱時，日期格式將區分大小寫。
其中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陳述式隱含或明確加以設定。
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兩個函數進行轉換，如下圖所示：
沒有留言:
張貼留言