文档首页/ 数据仓库服务 GaussDB(DWS)/ 更多文档/ 工具指南（巴黎区域）/ DSC SQL语法迁移工具/ Teradata语法迁移/ 日期和时间函数
更新时间：2023-03-17 GMT+08:00
查看PDF
分享

日期和时间函数

DATE

DSC支持迁移Teradata的SELECT语句中包含DATE FORMAT，使用TO_CHAR并以源格式显示日期。如果日期格式是一个表达式（例如：Start_Dt + 30）或者WHERE子句包含表达式（例如：WHERE Start_Dt > End_Dt），则不会执行此转换。

详情请参见转换类型为DATE（无DATE关键字）。

  • 无论SELECT语句是否有列别名，都可以进行迁移。
  • 子查询和内部查询不支持日期格式化，仅外部查询支持。
  • 关于日期格式化，如果建表时使用了模式名称，则后续SELECT查询仍需包含模式名称。在以下示例中，SELECT语句中的表TEMP_TBL不会迁移，原样保留。 
    1
2
3
4
5
6
    		 
    CREATE TABLE ${SCH}.TEMP_TBL
         (C1 INTEGER
         ,C2 DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX(C1,C2);
 
SELECT ${SCH}.TEMP_TBL.C2 FROM TEMP_TBL where ${SCH}.TEMP_TBL.C2 is not null;

输入：DATE FORMAT

1
2
3
4
5
6
7
8
9
 
SELECT 
          CASE 
               WHEN SUBSTR( CAST( CAST( SUBSTR( '20180631' ,1 ,6 ) || '01' AS DATE FORMAT 'YYYYMMDD' ) + abc_day - 1 AS FORMAT 'YYYYMMDD' ) ,1 ,6 ) = SUBSTR( '20180631' ,1 ,6 ) 
               THEN 1 
               ELSE 0 
          END 
     FROM 
          tab1 
;

输出

1
2
3
4
5
6
7
8
9
 
SELECT 
          CASE 
               WHEN SUBSTR( TO_CHAR( CAST( SUBSTR( '20180631' ,1 ,6 ) || '01' AS DATE ) + abc_day - 1 ,'YYYYMMDD' ) ,1 ,6 ) = SUBSTR( '20180631' ,1 ,6 ) 
               THEN 1 
               ELSE 0 
          END 
     FROM 
          tab1 
;

DSC支持迁移日期值。如果输入DATE后又输入“YYYY-MM-DD”，则输出中的日期不会改变。 以下示例显示DATE到CURRENT DATE的转换。

输入：DATE 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT 
        t1.c1 
        ,t2.c2 
  FROM 
        $schema.tab1 t1 
        ,$schema.tab2 t2 
  WHERE 
        t1.c3 ^ = t1.c3 
        AND t2.c4 GT DATE 
;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT 
        t1.c1 
        ,t2.c2 
  FROM 
        "$schema".tab1 t1 
        ,"$schema".tab2 t2 
 WHERE 
        t1.c3 <> t1.c3 
        AND t2.c4 > CURRENT_DATE 
;

输入：DATE和"YYYY-MM-DD"

1
2
3
4
5
 
ALTER TABLE 
     $abc . tab1 ADD ( 
          col_date DATE DEFAULT DATE '2000-01-01' 
     ) 
;

输出

1
2
3
4
5
 
ALTER TABLE 
     "$abc" . tab1 ADD ( 
          col_date DATE DEFAULT DATE '2000-01-01' 
     ) 
;

输入：DATE减法

1
2
3
4
5
6
7
 
SELECT
          CAST( T1.Buyback_Mature_Dt - CAST( '${gsTXDate}' AS DATE FORMAT 'YYYYMMDD' ) AS CHAR( 5 ) )
     FROM
          tab1 T1
     WHERE
          T1.col1 > 10
;

输出

1
2
3
4
5
6
7
 
SELECT
          CAST( EXTRACT( 'DAY' FROM ( T1.Buyback_Mature_Dt - CAST( '${gsTXDate}' AS DATE ) ) ) AS CHAR( 5 ) )
     FROM
          tab1 T1
     WHERE
          T1.col1 > 10
;

ADD_MONTHS

输入

1
 
ADD_MONTHS(CAST(substr(T1.GRANT_DATE,1,8)||'01'AS DATE FORMAT 'YYYY-MM-DD'),1)-1

输出

1
 
ADD_MONTHS(CAST(SUBSTR( T1.GRANT_DATE ,1 ,8 ) || '01' AS DATE ),1) - 1

TIMESTAMP

输入：TIMESTAMP

1
2
3
4
 
select CAST('20190811'||' '||'01:00:00' 
AS TIMESTAMP(0) 
FORMAT 'YYYYMMDDBHH:MI:SS'
) ;

输出

1
 
SELECT TO_TIMESTAMP( '20190811' || ' ' || '01:00:00' ,'YYYYMMDD HH24:MI:SS' ) ;

TIME FORMAT

输入

1
2
3
 
COALESCE(t3.Crt_Tm , CAST('00:00:00' AS TIME FORMAT 'HH:MI:SS'))
COALESCE(LI07_F3EABCTLP.CTLREGTIM,CAST('${NULL_TIME}' AS TIME FORMAT 'HH:MI:sS'))
trim(cast(cast(a.Ases_Orig_Tm as time format'hhmiss') as varchar(10)))

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
CAST('00:00:00' AS TIME FORMAT 'HH:MI:SS')
should be migrated as
SELECT CAST(TO_TIMESTAMP('00:00:00', 'HH24:MI:SS') AS TIME)
---
CAST(abc AS TIME FORMAT 'HH:MI:sS')
=>
CAST(TO_TIMESTAMP(abc, 'HH24:MI:SS') AS TIME)
---
CAST(abc AS TIME FORMAT 'HH:MI:sS')
=>
CAST(TO_TIMESTAMP(abc, 'HH24:MI:SS') AS TIME)

TIMESTAMP FORMAT

输入

1
2
3
4
5
6
7
 
select 
   a.Org_Id as Brn_Org_Id          /*        */
  ,a.Evt_Id as Vst_Srl_Nbr          /*       */
  ,a.EAC_Id as EAC_Id          /*    */
  ,cast(cast(cast(Prt_Tm as timestamp format 'YYYY-MM-DDBHH:MI:SS' ) as varchar(19) )as timestamp(0)) as Tsk_Start_Tm          /*        */
from ${BRTL_VCOR}.BRTL_BC_SLF_TMN_RTL_PRT_JNL as a          /* BC_           */
where   a.DW_Dat_Dt  = CAST('${v_Trx_Dt}' AS DATE FORMAT 'YYYY-MM-DD')  ;

输出

1
2
3
4
5
6
7
8
 
SELECT
          a.Org_Id AS Brn_Org_Id /*        */
          ,a.Evt_Id AS Vst_Srl_Nbr /*       */
          ,a.EAC_Id AS EAC_Id /*    */
          ,CAST( CAST( TO_TIMESTAMP( Prt_Tm ,'YYYY-MM-DD HH24:MI:SS' ) AS VARCHAR( 19 ) ) AS TIMESTAMP ( 0 ) ) AS Tsk_Start_Tm /*        */
     FROM ${BRTL_VCOR}.BRTL_BC_SLF_TMN_RTL_PRT_JNL AS a /* BC_           */
     WHERE
          a.DW_Dat_Dt = CAST( '${v_Trx_Dt}' AS DATE ) ;

TIMESTAMP(n) FORMAT

输入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
select 
   cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Snsh_Dt          /*      */
  ,coalesce(a.CRE_DAT,cast('0001-01-01 00:00:01' as timestamp(6) format 'yyyy-mm-ddbhh:mi:ssds(6)')) as Crt_Tm          /*      */
  ,cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_ETL_Dt          /*      */
  ,cast(current_date as date format 'yyyy-mm-dd') as DW_Upd_Dt          /*      */
  ,current_time(0) as DW_Upd_Tm          /*      */
  ,1 as DW_Job_Seq          /*      */
from ${NDS_VIEW}.NLV65_MGM_GLDCUS_INF_NEW as a          /*    MGM    */
;
-----------
cast('0001-01-01 00:00:00' as timestamp(6) format 'yyyy-mm-ddbhh:mi:ssds(6)')
TO_TIMESTAMP('0001-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS.US' )
----------
cast('0001-01-01 00:00:00.000000' as timestamp(6))
cast('0001-01-01 00:00:00.000000' as timestamp(6))
----------
CAST('0001-01-01 00:00:00.000000' AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)')
TO_TIMESTAMP('0001-01-01 00:00:00.000000', 'yyyy-mm-dd HH24:MI:SS.US' )
----------
cast(LA02_USERLOG_M.LOGTIME as TIMESTAMP(6) FORMAT 'YYYY-MM-DD HH:MI:SS.S(0)' )
TO_TIMESTAMP(LA02_USERLOG_M.LOGTIME, 'YYYY-MM-DD HH24:MI:SS' )
----------
cast('0001-01-01 00:00:00' as timestamp(3) format 'yyyy-mm-ddbhh:mi:ssds(3)')
TO_TIMESTAMP('0001-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS.MS' )
-----------
CAST( '0001-01-01 00:00:01.000000' AS TIMESTAMP ( 6 ) format 'yyyy-mm-ddbhh:mi:ssds(6)' )
TO_TIMESTAMP('0001-01-01 00:00:01.000000', 'yyyy-mm-dd HH24:MI:SS.US' )

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
cast('0001-01-01 00:00:00' as timestamp(6) format 'yyyy-mm-ddbhh:mi:ssds(6)')
TO_TIMESTAMP('0001-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS.US' )
----------
cast('0001-01-01 00:00:00.000000' as timestamp(6))
cast('0001-01-01 00:00:00.000000' as timestamp(6))
----------
CAST('0001-01-01 00:00:00.000000' AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)')
TO_TIMESTAMP('0001-01-01 00:00:00.000000', 'yyyy-mm-dd HH24:MI:SS.US' )
----------
cast(LA02_USERLOG_M.LOGTIME as TIMESTAMP(6) FORMAT 'YYYY-MM-DD HH:MI:SS.S(0)' )
TO_TIMESTAMP(LA02_USERLOG_M.LOGTIME, 'YYYY-MM-DD HH24:MI:SS' )
----------
cast('0001-01-01 00:00:00' as timestamp(3) format 'yyyy-mm-ddbhh:mi:ssds(3)')
TO_TIMESTAMP('0001-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS.MS' )
-----------
CAST( '0001-01-01 00:00:01.000000' AS TIMESTAMP ( 6 ) format 'yyyy-mm-ddbhh:mi:ssds(6)' )
TO_TIMESTAMP('0001-01-01 00:00:01.000000', 'yyyy-mm-dd HH24:MI:SS.US' )

trunc(<date>, 'MM') trunc(<date>, 'YY')

输入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
 
select 
   cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Stat_Dt          /*  */
  ,coalesce(d.IAC_Id,'') as IAC_Id          /*  */
  ,coalesce(d.IAC_Mdf,'') as IAC_Mdf          /*  */
  ,coalesce(c.Rtl_Wlth_Prod_Id,'') as Rtl_Wlth_Prod_Id          /*  */
  ,coalesce(c.Ccy_Cd,'') as Ccy_Cd          /*  */
  ,0 as Lot_Bal          /*  */
  ,cast(sum(case when s2.Nvld_Dt > cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') then s2.Pos_Amt else 0 end) as decimal(18,2)) as NP_Occy_TMKV          /*          */
  ,cast(
        sum(s2.Pos_Amt *
          ((case when s2.Nvld_Dt > cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') 
                    then cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') else s2.Nvld_Dt - 1 end)
          -
           (case when s2.Eft_Dt > trunc(cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd'),'MM')
              then s2.Eft_Dt else trunc(cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd'),'MM')
           end)
          + 1
          )
           ) 
/

输出

1
2
 
date_trunc('month', cast('${v_Trx_Dt}' as date))
date_trunc('year', cast('${v_Trx_Dt}' as date))

NEXT

输入：NEXT 
1
2
3
 
SELECT c1, c2 
  FROM tab1 
 WHERE NEXT(c3) = CAST('2004-01-04' AS DATE FORMAT 'YYYY-MM-DD');

输出

1
2
3
 
 SELECT c1, c2
  FROM tab1
 WHERE c3 + 1 = CAST('2004-01-04' AS DATE);
父主题： Teradata语法迁移