1

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!

Kris Van den Bergh
  • 1,102
  • 4
  • 18
  • 31
  • **Do not**. I repeat **do not** change the way you are storing this data. Instead change it in the display layer. Meaning in the report or application layer. Should be to difficult to convert and query time. – Zane Jan 08 '14 at 14:28

2 Answers2

3

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.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Well, if this is what it takes I will need to migrate the data using a different date type, i.e. Date and then SET DATEFORMAT accordingly on the database level in order – Kris Van den Bergh Jan 08 '14 at 15:13
  • 1
    @Kris please stop thinking about format at the database layer. Store your dates as dates; worry about format when you present / display. – Aaron Bertrand Jan 08 '14 at 15:15