Teradata Formats¶
Date in the YYYYMMDD Format¶
Input | Output |
|---|---|
SELECT 1 FROM tb_dt_fmtyyyymmdd
WHERE JobName ='${JOB_NAME}'
AND TXDATE = ${TX_DATE} - 19000000;
| SELECT 1 FROM tb_dt_fmtyyyymmdd
WHERE JobName ='${JOB_NAME}'
AND TXDATE = TO_DATE(${TX_DATE}, 'YYYYMMDD');
|
Date in the YYYYDDD Format¶
Input | Output |
|---|---|
REPLACE VIEW SC.VIEW_1
(
col_1
)
LOCKING TABLE sc.tab FOR ACCESS
AS
SEL
--tgt.col_1 is date type
CAST( CAST(TGT.col_1 AS DATE FORMAT 'YYYYDDD') AS CHAR(7) ) AS col_1
FROM
sc.tab TGT
;
| CREATE
OR REPLACE VIEW SC.VIEW_1 (col_1)
/*LOCKING TABLE sc.tab FOR ACCESS */
AS (
SELECT
/* tgt.col_1 is date type */
CAST( TO_DATE(TGT.col_1, 'YYYYDDD') AS CHAR( 7 ) ) AS col_1
FROM
sc.tab TGT
) ;
|
Column Names Starting with #¶
Input | Output |
|---|---|
REPLACE VIEW SC.VIEW_1
(
,col_1
,#_col_2
,#_col_3
)
LOCKING TABLE sc.tab FOR ACCESS
AS
SEL
Tgt.col1
,Tgt.#_col_2
,Tgt.#_col_3
FROM
sc.tab TGT
;
| CREATE
OR REPLACE VIEW SC.VIEW_1 (
,col_1
,"#_COL_2"
,"#_COL_3"
) /*LOCKING TABLE sc.tab FOR ACCESS */
AS (
SELECT
Tgt.col1
,Tgt."#_COL_2"
,Tgt."#_COL_3"
FROM
sc.tab TGT
) ;
|
Database Operations First During Type Casting¶
Input | Output |
|---|---|
REPLACE VIEW SC.VIEW_1
(
col_1
)
LOCKING TABLE sc.tab FOR ACCESS
AS
SEL
(COALESCE(TRIM(TGT.col_1),'')) || '_' || (COALESCE(TRIM(TGT.col_1),'')) (CHAR(22)) AS col_1
FROM
sc.tab TGT
;
| CREATE
OR REPLACE VIEW SC.VIEW_1 (col_1)
/*LOCKING TABLE sc.tab FOR ACCESS */
AS (
SELECT
CAST(
( COALESCE( TRIM( TGT.col_1 ) ,'' ) ) || '_' || ( COALESCE( TRIM( TGT.col_1 ) ,'' ) )
AS CHAR( 22 ) ) AS col_1
FROM
sc.tab TGT
) ;
|