文档首页 > > 工具指南> DSC SQL语法迁移工具> SQL语法迁移参考> Teradata语法迁移>

类型转换和格式化

更新时间：2020/04/21 GMT+08:00

本节主要介绍Teradata类型转换和格式化的迁移语法。迁移语法决定了关键字/特性的迁移方式。

在Teradata中，FORMAT关键词用于格式化字段/表达式。FORMAT '9(n)' 和'z(n)'分别使用0和空格（' '）填充，即使用LPAD函数。数据类型转换可通过CAST或直接指定数据类型实现：[like (expression1)(CHAR(n))]。DSC使用CAST完成。

DSC支持如下类型转换和格式化：

CHAR
COLUMNS和COLUMN ALIAS
表达式
INT
DATE
转换数据类型DAY为SECOND
DECIMAL
时间间隔
NULL
隐式类型转换

CHAR

输入：CHAR转换

1	(expression1)(CHAR(n))

输出

1	CAST( (expression1) AS CHAR(n) )

COLUMNS和COLUMN ALIAS

输入：对某列进行类型转换和格式化时，应确保列名和别名相同

SELECT Product_Line_ID, MAX(Standard_Price) 
  FROM ( SELECT A.Product_Description, A.Product_Line_ID
   , A.Standard_Price(DECIMAL(18),FORMAT '9(18)')(CHAR(18))  
        FROM product_t A
      WHERE Product_Line_ID in (1, 2) 
  ) AS tabAls 
 GROUP BY Product_Line_ID;

输出

SELECT Product_Line_ID, MAX( Standard_Price )
   FROM  ( SELECT A.Product_Description, A.Product_Line_ID
        , CAST( LPAD( CAST(A.Standard_Price AS DECIMAL( 18 ,0 )), 18, '0' ) AS CHAR( 18 ) ) AS Standard_Price 
                 FROM product_t A
               WHERE Product_Line_ID IN( 1 ,2 )
           ) AS tabAls
  GROUP BY Product_Line_ID;

表达式

输入：对表达式进行类型转换和格式化

1
2
3

SELECT product_id, standard_price*100.00(DECIMAL (17),FORMAT '9(17)' )(CHAR(17) ) AS order_amt 
   FROM db_pvfc9_std.Product_t 
  WHERE product_line_id is not null ;

输出

1
2
3

SELECT product_id, CAST(LPAD(CAST(standard_price*100.00 AS DECIMAL(17)), 17, '0') AS CHAR(17)) AS order_amt 
   FROM db_pvfc9_std.Product_t 
   WHERE product_line_id is not null ;

INT

输入：INT转换

SELECT
          CAST( col1 AS INT ) (
               FORMAT '9(5)'
          )
     FROM
          table1
;

输出

SELECT
          LPAD( CAST( col1 AS INT ) ,5 ,'0' )
     FROM
          table1
;

输入：INT转换

SELECT
          CAST( col1 AS INT ) (
               FORMAT '999999'
          )
     FROM
          table1
;

输出

SELECT
          LPAD( CAST( col1 AS INT ) ,6 ,'0' )
     FROM
          table1
;

输入：INT转换

SELECT
          CAST( expression1 AS INT FORMAT '9(10)' )
     FROM
          table1
;

输出

SELECT
          LPAD( CAST( expression1 AS INT ) ,10 ,'0' )
     FROM
          table1
;

输入：INT转换

SELECT
          CAST( expression1 AS INT FORMAT '9999' )
     FROM
          table1
;

输出

SELECT
          LPAD( CAST( expression1 AS INT ) ,4 ,'0' )
     FROM
          table1
;

DATE

在Teradata中对DATE进行格式转换时，使用AS FORMAT。DSC将添加TO_CHAR函数来保留指定的输入格式。

详情请参见日期和时间函数。

输入：数据类型转换，不使用DATE关键字

1
2
3

SELECT
      CAST( CAST( '2013-02-12' AS DATE FORMAT 'YYYY/MM/DD' ) AS FORMAT 'DD/MM/YY' )
;

输出

1
2
3

SELECT
      TO_CHAR( CAST( '2013-02-12' AS DATE ) ,'DD/MM/YY' )
;

转换数据类型DAY为SECOND

输入：DAY转换为SECOND

1 2	SELECT CAST(T1.Draw_Gold_Dt \|\| ' ' \|\|T1.Draw_Gold_Tm as Timestamp) - CAST(T1.Tx_Dt \|\| ' '\|\| T1.Tx_Tm as Timestamp) DAY(4) To SECOND from db_pvfc9_std.draw_tab T1;

输出

SELECT
          CAST(( CAST( T1.Draw_Gold_Dt || ' ' || T1.Draw_Gold_Tm AS TIMESTAMP ) - CAST(T1.Tx_Dt || ' ' || T1.Tx_Tm AS TIMESTAMP ) ) AS INTERVAL DAY ( 4 ) TO SECOND )
     FROM
          db_pvfc9_std.draw_tab T1
;

DECIMAL

输入：DECIMAL转换

SELECT
          standard_price (
               DECIMAL( 17 )
               ,FORMAT '9(17)'
          ) (
               CHAR( 17 )
          )
     FROM
          db_pvfc9_std.Product_t
;

输出

SELECT
          CAST( LPAD( CAST( standard_price AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS CHAR( 17 ) )
     FROM
          db_pvfc9_std.Product_t
;

输入：DECIMAL转换

SELECT
          standard_price (
               DECIMAL( 17 ,0 )
               ,FORMAT '9(17)'
          ) (
               VARCHAR( 17 )
          )
     FROM
          db_pvfc9_std.Product_t
;

输出

SELECT
          CAST( LPAD( CAST( standard_price AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS VARCHAR( 17 ) )
     FROM
          db_pvfc9_std.Product_t
;

输入：DECIMAL转换

SELECT
          customer_id (
               DECIMAL( 17 )
          ) (
               FORMAT '9(17)'
          ) (
               VARCHAR( 17 )
          )
     FROM
          db_pvfc9_std.Customer_t
;

输出

SELECT
          CAST( LPAD( CAST( customer_id AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS VARCHAR( 17 ) )
     FROM
          db_pvfc9_std.Customer_t
;

时间间隔

DDL和DML支持将数据类型转换为时间间隔。用户可在VIEW、MERGE和INSERT子查询和SELECT内进行转换。

输入：转换为时间间隔

1	SELECT TIME '06:00:00.00' HOUR TO SECOND;

输出

1	SELECT TIME '06:00:00.00';

输入：转换为时间间隔，使用TOP

1 2	SELECT TOP 3 * FROM dwQErrDtl_mc.C03_CORP_AGENT_INSURE WHERE Data_Dt > (SELECT TIME '06:00:00.00' HOUR TO SECOND);

输出

1	SELECT * FROM dwQErrDtl_mc.C03_CORP_AGENT_INSURE WHERE Data_Dt > (SELECT TIME '06:00:00.00') limit 3;

NULL

DSC将NULL(data_type)形式的表达式迁移为CAST(NULL AS replacement_data_type)。

输入：NULL转换

1	NULL(VARCHAR(n))

输出

1	CAST(NULL AS VARCHAR(n))

隐式类型转换

输入：隐式类型转换

SELECT Data_Type,Start_Dt,End_Dt
 FROM (
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT '101' AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-1 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
  ) TT
  UNION ALL
  SELECT '201' AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-7 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
  FROM Sys_Calendar.CALENDAR
  WHERE calendar_date = CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')
  AND Day_Of_Week = 1
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT '401' AS Data_Type,CAST('${TX_PRIMONTH_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_MONTH_END}' AS DATE FORMAT 'YYYYMMDD')
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT '501' AS Data_Type,CAST('${TX_PRIQUARTER_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_QUARTER_END}' AS DATE FORMAT 'YYYYMMDD')
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT '701' AS Data_Type,CAST('${TX_PRIYEAR_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_YEAR_END}' AS DATE FORMAT 'YYYYMMDD')
 ) T1
 ;

输出

SELECT Data_Type,Start_Dt,End_Dt
 FROM (
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT CAST('101' AS TEXT) AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-1 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
  ) TT
  UNION ALL
  SELECT CAST('201' AS TEXT) AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-7 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
  FROM Sys_Calendar.CALENDAR
  WHERE calendar_date = CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')
  AND Day_Of_Week = 1
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT CAST('401' AS TEXT) AS Data_Type,CAST('${TX_PRIMONTH_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_MONTH_END}' AS DATE FORMAT 'YYYYMMDD')
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT CAST('501' AS TEXT) AS Data_Type,CAST('${TX_PRIQUARTER_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_QUARTER_END}' AS DATE FORMAT 'YYYYMMDD')
  UNION ALL
  SELECT Data_Type,Start_Dt,End_Dt 
  FROM (
   SELECT CAST('701' AS TEXT) AS Data_Type,CAST('${TX_PRIYEAR_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt
   ) TT
  WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_YEAR_END}' AS DATE FORMAT 'YYYYMMDD')
 ) T1
 ;

父主题： Teradata语法迁移

上一篇：日期和时间函数

下一篇：BTEQ工具命令

文档是否有解决您的问题？

提交成功！非常感谢您的反馈，我们会继续努力做到更好！

反馈提交失败，请稍后再试！

如您有其它疑问，您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问