Find all date and time columns in Db2 database

Date and time in IBM Db2 are represented by following data types: DATE, TIME, TIMESTAMP

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


select c.tabschema as schema_name,
       c.tabname as table_name,
       c.colno as column_id,
       c.colname as column_name,
       c.typename as data_type,
       c.scale as timestamp_scale
from syscat.columns c
join syscat.tables t on c.tabschema = t.tabschema
                     and c.tabname = t.tabname
                     and t.type = 'T'
where c.typename in ('DATE', 'TIME', 'TIMESTAMP')
      and c.tabschema not like 'SYS%'
order by schema_name,


  • schema_name - name of the schema
  • table_name - name of the table
  • column_id - column position in table
  • column_name - name of the column
  • data_type - type of data
  • lentimestamp_scalegth - number of digits of fractional seconds for TIMESTAMP datatype


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

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.