关于TD cast的学习

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

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

 

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