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

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.