Date and time in Teradata are represented by common sql date types: DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIMEZONE and TIME WITH TIME ZONE and numerous of INTERVAL data types.

The query below lists all columns with date/time data types.

Query

SELECT  DatabaseName,
        TableName,
        ColumnName,
        CASE ColumnType
            WHEN 'DA' THEN 'Date'
            WHEN 'AT' THEN 'ANSI Time'
            WHEN 'TS' THEN 'Timestamp'
            WHEN 'TZ' THEN 'ANSI Time With Time Zone'
            WHEN 'SZ' THEN 'Timestamp With Time Zone'
            WHEN 'YR' THEN 'Interval Year'
            WHEN 'YM' THEN 'Interval Year To Month'
            WHEN 'MO' THEN 'Interval Month'
            WHEN 'DY' THEN 'Interval Day'
            WHEN 'DH' THEN 'Interval Day To Hour'
            WHEN 'DM' THEN 'Interval Day To Minute'
            WHEN 'DS' THEN 'Interval Day To Second'
            WHEN 'HR' THEN 'Interval Hour'
            WHEN 'HM' THEN 'Interval Hour To Minute'
            WHEN 'HS' THEN 'Interval Hour To Second'
            WHEN 'MI' THEN 'Interval Minute'
            WHEN 'MS' THEN 'Interval Minute To Second'
            WHEN 'SC' THEN 'Interval Second'
            END AS DataType
FROM    DBC.ColumnsV
WHERE   ColumnType in ('DA', 'AT', 'TS', 'TZ', 'SZ', 'YR', 'YM', 'MO',
        'DY', 'DH', 'DM', 'DS', 'HR', 'HM', 'HS', 'MI', 'MS', 'SC')
        AND DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr',
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB',
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'TDStats', 'tdwm', 'SQLJ', 'SYSSPATIAL','TD_SYSFNLIB')
ORDER BY    DatabaseName,
            TableName;

Columns

• DatabaseName - name of the database
• TableName - name of the table
• ColumnName - name of the column
• DataType - type of data:
  • Date
  • ANSI Time
  • Timestamp
  • ANSI Time With Time Zone
  • Timestamp With Time Zone
  • Interval Year
  • Interval Year To Month
  • Interval Month
  • Interval Day
  • Interval Day To Hour
  • Interval Day To Minute
  • Interval Day To Second
  • Interval Hour
  • Interval Hour To Minute
  • Interval Hour To Second
  • Interval Minute
  • Interval Minute To Second
  • Interval Second

Rows

• One row represents one column with a date/time data type
• Scope of rows: all columns containing date/time data types in the database
• Ordered by database name and table name

Sample results