CODEWITHSUNDEEP
sqlsql-serverdatesql-convert
aucuparia
aucuparia

Reputation: 2051

What is SQL Sever's default format for converting dates from strings

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.

Upvotes: 1

Views: 42

Answers (1)

SqlZim
SqlZim

Reputation: 38023

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;

Upvotes: 1

Related Questions