Find all date and time columns in SQL Server database

Date and time in SQL Server are represented by following data types: date, time, datetime, datetime2, smalldatetime, datetimeoffset, . The query below lists all columns with date/time data types.


select schema_name(t.schema_id) + '.' + as [table],
       c.column_id, as column_name,
       type_name(user_type_id) as data_type,
       scale as second_scale
from sys.columns c
join sys.tables t
     on t.object_id = c.object_id
where type_name(user_type_id) in ('date', 'datetimeoffset', 
      'datetime2', 'smalldatetime', 'datetime', 'time')
order by [table],


  • database_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
  • second_scale - number of digits for the fractional part of the seconds


  • 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 schema name, table name and column position in table

Sample results

There are no comments. Click here to write the first comment.