When you connect to SQL Server, usually the date format is determined by your language. The default language for a session is the language for that session’s login, unless overridden on a per-session basis by using the Open Database Connectivity (ODBC) or OLE DB APIs.

The date format setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.

Despite the fact that the your session’s language determines the date format, you can override the date format if required. For example, if your language is us_english, the date format will be mdy (so that 07/01/2018 represents the 1st of July and not the 7th of January). You can change this so that the date format is dmy (or any other format) while the language remains us_english.

You can use T-SQL to explicitly set the date format of the current session by using the SET DATEFORMAT statement.

Syntax

Here’s how the syntax goes:

SET DATEFORMAT { format | @format_var }

Where format | @format_var is the order of the date parts.

Valid values are mdy, dmy, ymd, ydm, myd, and dym. These can be either Unicode or double-byte character sets (DBCS) converted to Unicode.

However, note that  ydm is not supported for date, datetime2 and datetimeoffset data types.

Example

Before we change anything, let’s look at the current user options. This will tell us what the current language is and the date format (as well as a few other things):

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 |
+-------------------------+----------------+

We can see that the language is us_english and the date format is mdy (which is the default date format for that language).

So let’s change the date format, then check the user options again:

SET DATEFORMAT dmy;
DBCC USEROPTIONS;

Result:

 
+-------------------------+----------------+
| Set Option              | Value          |
|-------------------------+----------------|
| textsize                | -1             |
| language                | us_english     |
| dateformat              | dmy            |
| 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 |
+-------------------------+----------------+

And we can see that the date format has changed, while the language remains the same.

So this is how to change the date format without changing the language. As mentioned, if you change the language, it will implicitly set the date format at the same time. If that’s what you want to do, check out How to Set the Current Language in SQL Server (T-SQL).