SSMS - MS SQL Sever Query option set ON/OFF to display all columns in Shortdate format?

Question

In SSMS, for MS SQL Server 2008 or newer versions, is there a general query option or something like that, something to set ON or OFF before launching the query, in order to view all DATE columns as Shortdate (only date, without time)? Something like SET ANSI_NULLS { ON | OFF } ?

Because I often use 'select * from table', or different approaches like that, and inside tables are many columns and the DATE columns are in different places, and I don't want every time to check where these columns are and to explicitly use CONVERT or CAST only on them, to display them properly.

Thank you for any suggestion.

Answer 1

Yeah I will solve such situation from interface end only.

Also saying like,

Because I often use 'select * from table', or different approaches

this is itself bad,you can't have your own way or approaches.

Nonetheless in sql we can do something like this,

USE AdventureWorks2012
    GO

    --proc parameter
    DECLARE @tablename VARCHAR(50) = 'Employee'
    DECLARE @table_schema VARCHAR(50) = 'HumanResources'
    --local variable
    DECLARE @Columnname VARCHAR(max) = ''
    DECLARE @Sql VARCHAR(max) = ''

    SELECT @Columnname = @Columnname + CASE 
            WHEN DATA_TYPE = 'date'
                OR DATA_TYPE = 'datetime'
                THEN 'cast(' + QUOTENAME(COLUMN_NAME) + ' as date)'
            ELSE QUOTENAME(COLUMN_NAME)
            END + ',' + CASE 
            WHEN DATA_TYPE = 'date'
                OR DATA_TYPE = 'datetime'
                THEN 'cast(' + QUOTENAME(COLUMN_NAME) + ' as date)'
            ELSE QUOTENAME(COLUMN_NAME)
            END + ','
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @tablename
        AND TABLE_SCHEMA = @table_schema
    ORDER BY ORDINAL_POSITION

    SET @Columnname = STUFF(@Columnname, len(@Columnname), 1, '')
    --set @Columnname=stuff(@Columnname,1,1,'')
    --PRINT @Columnname
    SET @Sql = 'select ' + @Columnname + ' from ' + @table_schema + '.' + @tablename + ''

    --PRINT @Sql
    EXEC (@Sql)

it can be further improve as per requirement.Also please use sp_executeSql you can customize case condition.