1

I'm trying to work with a 3rd party application which stores dates (can be entered by the user, in some cases with little validation) in a varchar column, then uses CONVERT( DATETIME, MY_COLUMN ) - without an explicit format - when it wants to use them as dates. Ugh.

Unsurprisingly this often fails. But not always. What is SQL's default format for CONVERT if you don't specify one? It appears to be 101 (i.e. mm/dd/yyyy), but is this always the case? Does it depend on the database or server collation or some other setting? The MSDN CAST and CONVERT docs don't say.

We're using SQL Server 2014.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
aucuparia
  • 2,021
  • 20
  • 27

1 Answers1

1

For the default, it depends on the default language setting at the server or login level, which implicitly sets the dateformat, datefirst, as culture related settings. You can also change the defaults based on login. e.g.

alter login [aucuparia] with default_language=[british];

These can be changed at the session level with set language, set dateformat, and set datefirst.

To see the language setting for the current connection:

select @@language;

To see settings for each language:

select langid, alias, DateFormat, DateFirst, months, shortmonths, days 
from sys.syslanguages;
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • I don't think `datefirst` is relevant to this question. – Roger Wolf Mar 24 '17 at 05:34
  • @RogerWolf I included it as part of the answer because it is also set implicitly by the language setting and effects the results of date and time functions concerning the `week` date part. – SqlZim Mar 24 '17 at 11:59