Datetime2 format to dd/mm/yyyy

Question

I have a column in a SQL Server 2008 datatabase that is of the Data Type datetime2. This means my dates are displayed in the format YYYY-MM-DDThh:mm:ss. However, I need my dates to be in the format dd/mm/YYYY by default and I cannot change the column datatype. Also, the datetime2 format is not affected by the SET LANGUAGE and SET DATEFORMAT session locale settings. Finally, I do not want to use the convert() functionality as otherwise I need to rewrite all of my queries.

How can this still be achieved?

Many thanks!

Answer 1

When you say "displayed" - displayed where? In SSMS? Then you'll need to change your query. In an app/report? Then change the app code/report to properly format the date.

Dates are Dates in SQL - the "format" is only a consideration when you visualize the date. If you don't specify a format (using CONVERT), the "default" format for datetime2 will be used. There's no system-wide setting to override that default for datetime2 that I'm aware of.

Answer 2

Stop thinking about how dates are stored. If you really want your dates displayed as dd/mm/yyyy, then apply that formatting at the last possible step (e.g. using Format() or ToString() in C#). Or if you need this at the query level, then write your queries properly - you will have to go back and add CONVERT(), e.g.:

DECLARE @d DATETIME2 = CURRENT_TIMESTAMP;
SELECT CONVERT(CHAR(10), @d, 103);

It would be completely wrong to change the data type to somehow mangle this data into the wrong data type just so you could enforce your preferred format at the database level.