Teradata Tips Info

Teradata has two types of time types, the old one that is simply an integer format ’99:99:99′ and the new ANSI TIME type. The integer-based one can be used for comparisons but not for time calculations. The new one can be used for everything. This is how you can convert to the new type if you have the old type. Assume you have a table called T with an integer-based column UPDATE_TM like:

CREATE TABLE T
(
   UPDATE_DT DATE FORMAT 'YYYY-MM-DD',
   UPDATE_TM INTEGER FORMAT '99:99:99' )
);

To convert the integer time UPDATE_TM to a TIME:

SELECT
  TIME '00:00:00'
  + CAST(update_tm / 10000 AS INTERVAL HOUR)
  + CAST((update_tm / 100) MOD 100 AS INTERVAL MINUTE)
  + CAST((update_tm MOD 100) AS INTERVAL SECOND)
FROM T

To convert the UPDATE_DT, UPDATE_TM to a timestamp(0):

SELECT 
  CAST(update_dt AS TIMESTAMP(0))
  + CAST(update_tm / 10000 AS INTERVAL HOUR)
  + CAST((update_tm / 100) MOD 100 AS INTERVAL MINUTE)
  + CAST((update_tm MOD 100) AS INTERVAL SECOND)
FROM T

CREATE TABLE T( dt DATE, tm TIME(0));
 
INSERT INTO T VALUES (DATE '2009-05-28', TIME '14:53:45');
 
SELECT dt, tm,
    CAST(  dt AS TIMESTAMP(0)) +
            (tm - TIME '00:00:00' HOUR TO SECOND) ts
 FROM T;

        dt        tm                   ts
----------  --------  -------------------
2009/05/28  14:53:45  2009-05-28 14:53:45

Use this to find out how much perm space the tables in a specific database are using in megabytes:

SELECT tablename,SUM(currentperm)/1024/1024 MB
FROM dbc.allspace
WHERE databasename='your_database_name'
GROUP BY 1
ORDER BY 2 DESC;

To convert a decimal(n) value to a simple string containing only digits, use this:

SELECT
  CAST( CAST( d AS format 'Z(I)' ) AS VARCHAR(30) )
FROM T;

Why do you need the double cast? Well, if you try to convert from a column that has a decimal(n) to a numeric string and you simply cast to a varchar, Teradata will add a decimal point at the end of your string.

CREATE TABLE T(d DECIMAL(18));
INSERT INTO T VALUES(42);
INSERT INTO T VALUES(3);
 
SELECT '['||CAST(d AS VARCHAR(20))||']' FROM T;

(('['||d)||']')
----------------------
[3.]
[42.]

SELECT
  '['||CAST(CAST(d AS format 'Z(I)') AS VARCHAR(20)) ||']'
FROM T;

(('['||d)||']')
----------------------
[3]
[42]