SQLTeam.com | Weblogs | Forums

Is it possible to return all dates in 2020-01-01 format?


When I do SELECT * or SELECT [several specific columns], is there an one line code that I can use so that all date columns are returned in YYYY-MM-DD format?


The date formats are based off the regional settings of the server. When I run a Select * on a table, the formats that come back are YYYY-MM-DD by default. That being said, if you need a certain format, I would use convert and format the dates the way I want them and not make a global change

On my system - SELECT CAST(column AS date).

But...dates do not have a format in SQL Server. You can display a date in different formatted strings using CONVERT or FORMAT but then the data is no longer a date - it is a string.

My results come back as:
2020-01-01 01:01:01.000

The .000 as well as the 01:01:01 is very inconvenient to work with Excel so I have to paste in Notepad++, search-and-replace, and then paste in Excel.

I want to save that time, but without spending time to CONVERT() each column (or when I do SELECT *).

Is that possible?

So now the issue is related to Excel? Not sure how or why your results are coming up with hours, minutes and seconds if you are converting to a date.

As for Excel - exporting to Excel (or copy/paste) requires a conversion to a date (for just the date) - or to a string that does not include the sub-seconds. There is no magic button that will do that for you - you must write the code.