TERADATA: CAST & Formatting

CAST & Formatting in Teradata

Friday, January 23, 2015

2:28 PM

Numbers:

SELECT CAST(20000 AS DECIMAL(10,3) FORMAT 'Z9.999') AS "Decimal Rounded"
      ,20000 (FORMAT 'Z,ZZZ,ZZ9.999') (VARCHAR(30)) AS "Decimal Text";

 Decimal Rounded Decimal Text 

 --------------- -------------

       20000.000    20,000.000

TimeStamp:

SELECT CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS') AS "TIMESTAMP";

TIMESTAMP

---------------------

2015-01-23 15:33:21.0

SELECT TIMESTAMP '9999-12-31 00:00:00' AS "TIMESTAMP";

TIMESTAMP

---------------------

9999-12-31 00:00:00.0

SELECT CAST(CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS') AS CHAR(19)) AS "TIMESTAMP";

TIMESTAMP          

-------------------

2015-03-12 17:22:24

SELECT CURRENT_TIMESTAMP(0) (FORMAT 'YYYY-MM-DDBHH:MI:SS') (CHAR(19)) AS "TIMESTAMP"

TIMESTAMP          

-------------------

2015-03-12 17:22:24

SELECT CAST(CAST(CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS') AS CHAR(14)) AS BIGINT) AS "TIMESTAMP"

TIMESTAMP          

-------------------

20150312172224

DATE to CHAR:

SELECT CAST(CAST(CURR_DT AS FORMAT 'YYYY-MM-DD') AS CHAR(10))                        AS CURR_DT
      ,COALESCE(CAST(CAST(NULL_DT AS FORMAT 'YYYY-MM-DD') AS CHAR(10)), 'NULL DATE') AS NULL_DT
  FROM (SELECT CAST('2015-01-01' AS DATE) AS CURR_DT
              ,CAST(NULL AS DATE)         AS NULL_DT) Tbl;

 CURR_DT    NULL_DT  

 ---------- ---------

 2015-01-01 NULL DATE

DATE Arithmetic:

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0))                         (FORMAT 'YYYY-MM-DDBHH:MI:SS') (CHAR(19)) AS TODAY
      ,(CAST(CURRENT_DATE AS TIMESTAMP(0)) - INTERVAL '1' SECOND) (FORMAT 'YYYY-MM-DDBHH:MI:SS') (CHAR(19)) AS YESTERDAY

TODAY               YESTERDAY          

------------------- -------------------

2015-03-17 00:00:00 2015-03-16 23:59:59