teradata CAST

1）、數值型截位或四捨五入
SEL CAST(50500.75 AS INTEGER) AS "Truncated”
,CAST(50500.75 AS DECIMAL(6,0)) AS "Rounded";

Truncated Rounded
----------- ----------
50500 50501.

[@more@]

SEL CAST(6.74 AS DECIMAL(2,1)) AS "Drop Precision”
,CAST(6.75 AS DECIMAL(2,1)) AS "Round Up”
,CAST(6.85 AS DECIMAL(2,1)) AS "Round Down to Even";

Drop Precision Round Up Round Down to Even
----------------- ------------ ------------------------
6.7 6.8 6.8

2）、字元型截位

SEL 'Teradata'(char(4)) as "Truncate with CAST”
,'Teradata'(char(4),UPPERCASE) as "Truncate and UPPERCASE";

Truncate with CAST Truncate and UPPERCASE
---------------------- ------------------------------
Tera TERA

3）、數值型進行格式變換
Numeric formatting symbols:
$ Fixed or floating dollar sign
9 Decimal digit (no zero suppress)
Z Zero-suppressed decimal digit
, Comma – inserted where specified
. Decimal point position
- Dash character – inserted where specified
/ Slash character – inserted where specified
% Percent character – inserted where specified

SELECT 123 (FORMAT '99999999'), 123 (FORMAT '9(8)')
,000005 (FORMAT 'Z(5)9'), 1234.56 (FORMAT '$$$,$$9.99')
,5 (FORMAT 'Z9%'), 2225551212 (FORMAT '999/999-9999');

123 123 5 1234.56 5 2225551212.
-------- -------- ------ ---------- --- ------------
00000123 00000123 5 $1,234.56 5% 222/555-1212

將DECIMAL型轉成字元型:
不要用這種辦法：
TRIM(TRAILING '.' FROM TRIM(CAST(P1.Acct_Item_Type_Id AS CHAR(20)))
要採取這種辦法：
CAST(cast(P1.Acct_Item_Type_Id AS format ‘z(18)') as CHAR(20))

日期型進行格式變換
Date formatting symbols:
Y Year as YYYY/Y4 (2004) or YY (04)
M Month as MMMM/M4 (August), MMM/M3 (Aug) or MM (08)
D Day as DDD/D3 (day of the year) or DD (day of the month)
E Day of the week as EEEE/E4 (Monday) or EEE/E3 (Mon)
, Comma – inserted where specified
. Decimal point position
- Dash character – inserted where specified
/ Slash character – inserted where specified
B Blank position – inserted where specified

SELECT CAST(current_date AS FORMAT 'YYYYMMDD')
,CAST(current_date AS FORMAT 'MMMBDD,BYYYY')
,CAST(current_date AS FORMAT 'M4BDD,BY4')
,CAST(current_date AS FORMAT 'YYDDD');

Date Date Date Date
----------- ---------------- ------------------- -----
20040814 Aug 14, 2004 August 14, 2004 04227