1

I have got several tables in my database (with data) that is formatted in the American standard of mm/dd/yyyy. Is there a way to convert the date to a British format (i.e. dd/mm/yyyy) that doesn't involve dropping and recreating the tables?

Thanks!

AgentNo
  • 231
  • 5
  • 20
  • 3
    First how do you store dates (DATETIME/DATETIME2/VARCHAR)? Second mm/dd/yyyy is only representation – Lukasz Szozda Apr 29 '18 at 18:35
  • Can you share your DDL (CREATE TABLE statements)? – MJH Apr 29 '18 at 18:45
  • 4
    [Dates are not stored with display format.](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) Only string representation of dates have display format. – Zohar Peled Apr 29 '18 at 19:03
  • 1
    It does indeed sound like you have the dates stored as a `varchar` not a `date` (dates in SSMS are generally returned in the format `yyyy-mm-dd`, so that they can be unambiguous). Always store your data as a data type representative for it. You'll need to add a new column to your table, converting the value of your current column to a date for its value, then drop your old column. If you want it to have the same name, you can then rename the column. – Thom A Apr 29 '18 at 19:08
  • What do you mean? If you insert `31/12/2001` it fails and tells you that is an invalid date? This depends on the default language of your login – Martin Smith Apr 29 '18 at 19:44
  • in `SQL` Date is Date regardless of how it was stored...in the end you could always `convert` to desired format but if you are matching dates then always `cast` to the desired type – RoMEoMusTDiE Apr 29 '18 at 21:06

4 Answers4

5

I set my data type to >date when I was creating my table. I can store dates in the format mm/dd/yyyy, but not dd/mm/yyyy.

As I've mentioned in my comment, dates are not stored with their display format - in fact, you can say that dates have no display format - only string representation of dates have a display format.

Whenever dealing with string literals representing date and datetime values in SQL server, use ISO 8601 datetime format (yyyy-MM-ddTHH:mm:ss or yyyyMMddTHHmmss).
SQL Server guarantees to properly parse this string representation into date / datetime values, without ambiguity.

Please note the T seperator between the date and the time. There is a very similar standard format, where the T is replaced with a white-space, but the DateTime data type have a bug parsing this format - and it is culture-dependent (Note that DateTime2 does not have that bug) - and that's another reason why you should never use datetime again.

When you use a string literal like '25/03/2018' it's easy for a human to see that it stands for March 25th 2018, but SQL Server will raise an error trying to parse this string into a date if the current value of DATEFORMAT is not DMY.

However, SQL Server will always parse ISO 8601 string representation of dates correctly, regardless of any local settings or previous set dateformat or set language statements etc'. '2018-02-01T15:40:50' will always be parsed is February 1st 2018, 3:40:50 PM.

Unless specified, As Martin Smith wrote in his comment, the default dateformat depends on the defualt language settings of the current login - so a query that works for one login might raise an error for another login - and that's another good reason never to trust culture-specific string representation of datetime.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • A little analogy to maybe clarify this a little: An integer number is an integer number. Regardless whether you use arabic numerals, or roman numerals or just tally to represent it. Same with different words for the number in different languages. – sticky bit Apr 29 '18 at 23:11
3
DECLARE @dt DATETIME = '01/20/2019';
SELECT FORMAT( @dt, 'd', 'en-gb' ) AS 'UK'
giorgi02
  • 683
  • 6
  • 13
2

Are you referring to the date format displayed by SQL Server Management Studio or a similar application? The format is controlled by Windows Control Panel settings, not by SQL Server. There is no internal format for dates in SQL Server.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • That's not entirely accurate. Dates do not store *display format*, but they are, of course, stored in a specific format internally... – Zohar Peled Apr 29 '18 at 19:22
  • @Zohar, Dates are stored as binary but the OP is referring to the British or American format which does not apply to storage. – nvogel Apr 29 '18 at 21:04
  • That's my point exactly - *storage format* have nothing to do with *display format*. – Zohar Peled Apr 29 '18 at 21:06
0

This is defined by default from the machine where is running MS SQL Server.

To see all available cultures please do:

select * from sys.syslanguages

Then, you can change SQL Server language using:

SET LANGUAGE BRITISH

... and the date format will always be like you want.

Note: this will change all the database (not just the date format), the other way is to change the date format using the FORMAT function in T-SQL.

Horacio Garza
  • 45
  • 2
  • 7