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

Question

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

Because I often use 'select * from table' or different approaches like this, and there are a lot of columns inside tables, and DATE columns are in different places, and I do not want to check every time where these columns and use CONVERT or CAST only on them to display them correctly.

Thanks for any suggestion.

Answer 1

Yes, I will only allow this situation from the end of the interface.

As well as,

Since I often use "select * from table" or different approaches

this in itself is bad; you cannot have your own approach or approaches.

However, 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 improved as per requirement. Also use sp_executeSql you can customize the condition condition.

Answer 2

SSMS does not have a button or a magic display function. When you execute the query, SSMS will display this column in a format suitable for this data type; for the datetime field, which will include the time.

If you do not want to include time, then you need either CAST or CONVERT separate columns, or format the data accordingly at the presentation level (for example, if you are using Excel, then dd / MM / yyyy may be appropriate).

If all your columns have '00:00:00.000' at the end of their value, the problem is not in the display format, this is your choice of data type. Obviously, the problem is not that SSMS returns the time for the date**time** column, it means that you declared the column as datetime when it should have been date . You can change the data type of a column using ALTER . For instance:

 USE Sandbox; Go CREATE TABLE TestTable (ID smallint IDENTITY(1,1), DateColumn datetime); INSERT INTO TestTable (DateColumn) VALUES ('20180201'),('20180202'),('20180203'),('20180204'),('20180205'); SELECT * FROM TestTable; GO ALTER TABLE TestTable ALTER COLUMN DateColumn date; GO SELECT * FROM TestTable; GO DROP TABLE TestTable; 

TL DR: SSMS displays the data in the appropriate format for the data you have. If you do not like this, you should provide an alternative format for display for each respective column. If the problem is with your data, change the data type.

---

Edit: I wanted to add a little more.

This question is very similar to the question: "I would like to be able to run queries where decimal numbers return only the integer part of the value. Can this be done automatically?" Thus, a value of 9.1 will return 9, but also a value of 9.999999999 will return 9.

Now I understand that you "can" think "the numbers are not the same as the dates," but in fact they are. At the end (especially in the data), the date is just a number (hell, the datetime in SQL Server is stored as the number of days after 1900-01-01 , and the time is the decimal number of this number, so 43136.75 is actually 2018-02-07 18:00:00.000 ).

Now that we are talking in numbers, does it seem like a good idea for all your decimal places to return as their FLOOR value? I think the answer is no. Imagine doing some kind of accounting and summing up transaction values ​​using the FLOOR value. You can lose 1000 (or more) of £ / $ / €.

Think of an old example of people who stole money from payments that contained values ​​less than a penny. The amount they stole was a huge amount, however, no individual theft mattered> = = $ 0.01. The same principle does apply here; accuracy is very important, and if your column has such accuracy, it should be there for some reason.

The same applies to dates. If you save time with dates, and time is not suitable for this particular request, change your request; with setting to ignore times (or decimal points) is, frankly, just a bad idea.

Answer 3

I do not think that in SSMS there is such an option. The best I came up with is to create table views, and so you can do

 select convert(date, <date column>) 

once, and they will be displayed as soon as the dates in the views.