When using SQL Server, your current connection includes a number of options that determine things like the language, date formats, etc. These could be set at whatever the default is, but they can also be overridden during the session by using a SET statement.
The date format affects the interpretation of character strings as they are converted to date values for storage in the database. When the language is set using SET LANGUAGE, the date format setting is implicitly set accordingly. This can be explicitly overridden with the SET DATEFORMAT statement.
In any case, you can find the current date format by using the DBCC USEROPTIONS command. This command returns the SET options that have been set for the current connection.
Syntax
The syntax goes like this:
DBCC USEROPTIONS [ WITH NO_INFOMSGS ]
The optional NO_INFOMSGS argument suppresses all informational messages that have severity levels from 0 through 10.
Example
Here’s an example of running the command, and the results I get on my current test machine:
DBCC USEROPTIONS;
Result:
+-------------------------+----------------+ | Set Option | Value | |-------------------------+----------------| | textsize | -1 | | language | us_english | | dateformat | mdy | | datefirst | 7 | | lock_timeout | 5000 | | quoted_identifier | SET | | arithabort | SET | | ansi_null_dflt_on | SET | | ansi_warnings | SET | | ansi_padding | SET | | ansi_nulls | SET | | concat_null_yields_null | SET | | isolation level | read committed | +-------------------------+----------------+
As mentioned, you can change these options with the SET statements.
Here’s an example of changing the language of the current session, then running the command again:
SET LANGUAGE German; DBCC USEROPTIONS;
Result:
+-------------------------+----------------+ | Set Option | Value | |-------------------------+----------------| | textsize | -1 | | language | Deutsch | | dateformat | dmy | | datefirst | 1 | | lock_timeout | 5000 | | quoted_identifier | SET | | arithabort | SET | | ansi_null_dflt_on | SET | | ansi_warnings | SET | | ansi_padding | SET | | ansi_nulls | SET | | concat_null_yields_null | SET | | isolation level | read committed | +-------------------------+----------------+ Die Spracheneinstellung wurde in Deutsch geändert.
So we can see that not only did the language change, but the date format also changed.
However, if this is not the desired date format, the date format can be explicitly changed using SET DATEFORMAT.
Like this:
SET DATEFORMAT mdy; DBCC USEROPTIONS;
Result:
+-------------------------+----------------+ | Set Option | Value | |-------------------------+----------------| | textsize | -1 | | language | Deutsch | | dateformat | mdy | | datefirst | 1 | | lock_timeout | 5000 | | quoted_identifier | SET | | arithabort | SET | | ansi_null_dflt_on | SET | | ansi_warnings | SET | | ansi_padding | SET | | ansi_nulls | SET | | concat_null_yields_null | SET | | isolation level | read committed | +-------------------------+----------------+
And we can see that the date format has been changed as specified, while leaving the language as it is.
The sys.dm_exec_requests View
You can also return the date format being used in the current request by querying the sys.dm_exec_requests system view. This view returns a nice big bunch of columns, but you can narrow it down to only those columns you’re interested in. In our case, we’re only interested in one column – the date_format column:
SELECT r.date_format FROM master.sys.dm_exec_requests r WHERE r.session_id = @@SPID;
Result:
+---------------+ | date_format | |---------------| | mdy | +---------------+
I also narrowed the query down to just the current request. I did this by using @@SPID, which returns the session ID of the current user process.