Change date format to a column type date sql server

According to your comment to the answer of Andoni Alda, it seems that your error is more in the recording than in the query, that is, you are trying to record a string instead of a DateTime. It may work on your computer when you record it as dd / mm / yyyy in string format because your database server is configured for Spanish and therefore does not give you problems but when recording as mm / dd / yyyy you can find dates of type 03/31/2016 that will give you an exception (apart from the fact that the rest of the dates will be badly recorded).

If you are recovering the date of a DatePicker control or similar, the value that you return is already a DateTime and this is the one that you should record without formatting it previously. If this is not the case and you are getting the string from a file or similar, you can always convert it to DateTime using a Parse or TryParse method:

 DateTime dtmDate = DateTime.Parse(strFecha);

Once you have a dateTime, you do not have to convert it back to a string, simply use Command parameters in your recording. That is, instead of:

string strSQL = string.format("INSERT INTO tabla (Fecha) VALUES {0:dd-MM-yyyy HH:mm}", dtmDate);

using (SqlConnection objConnection = new SqlConnection(connectionString))
    { command = new SqlCommand(strSQL, objConnection);
        objConnection.Open();
        command.Execute(strSQL);
    }

Use parameters:

string strSQL = "INSERT INTO tabla (Fecha) VALUES @FE_Fecha";
using (SqlConnection objConnection = new SqlConnection(connectionString))
{ command = new SqlCommand(strSQL, objConnection);
  command.Parameters.Add("@FE_Fecha", SqlDbType.DateTime);
  command.Parameters["@FE_Fecha"].Value = dtmDate;
  objConnection.Open();
  command.Execute();
}

Where @FE_Date is the parameter that SQL Server will use as a date.

Note: I have written the code without testing, see MSDN for more information.

Note 2: Another option that I have used for a long time if you do not want to use parameters (which I would not understand) is to record from a string is to do it in canonical ODBC format, that is: yyyy-mm-dd hh: mm: ss (2016-12-03 19:23:00 for example)

Dates (type Date, DateTime, and the like) are not saved with any format in the database engine. In the database it is stored in a binary way.

Internal Representation

In fact, for the curious, you know that SQL Server for the type of DateTime stores the date in a sequence of 8 bytes. Divided into two 4-byte numbers, the first indicates the number of days elapsed since epoch (January 1, 1900), and the other stores the amount of clock ticks > after midnight. Each Tick of the watch measures 1/300 of millisecond.

Then why in X tool do I see it with Y format?

When you select the database from the SQL Server Management Studio or another tool, it is this tool that you are using that will represent that date as text and, in general, this tool will apply the date format of the machine where it is executed or the default format configured in the database. This default format is only used so that date data can be interpreted that there is no other date to be represented as text, but the recommendation is to handle them as they are, dates.

What I do in my programming language

Read the documentation. The languages that provide high level interfaces with SQL server, will usually return this data in a variable with a data type date native language, and from there again you are responsible for give a textual representation.

But I need the SQL server to return the date in X format

I do not recommend it, but if you do not have another, ask SQL Server to return not a date, but a text. To have some control over the format, use the Convert function, like this:

select convert(varchar(20), MiFecha, 101)
  from MiTabla;

You can try this:

SELECT replace(convert(NVARCHAR, getdate(), 106), ' ', '/')

Or use format:

SELECT FORMAT(getdate(),'yyyy/mm/dd') as date