Find all date and time columns in Azure SQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-02-08

Table of Contents:


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

    Query

    select schema_name(t.schema_id) + '.' + t.name as [table],
           c.column_id,
           c.name 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],
             c.column_id;
    

    Columns

    • 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

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

    Sample results

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept