Columns in tables are data. Data has no formats. Internally it is even possible for some specific data to have some format but this only occurs with text and even then it is something that matters for the data itself and not for the column.

In a column of type DateTime there is a date. Point.

If you want in some format, you should get the date and create a formatted text as you wish. But formatted text is different from a date. The software does not care about formats, this is used to show for humans. And what is shown to humans is always a text, even if this text represents a date. But this is just a representation.

One way to get a formatted output would look like this:

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]

Documentation for CONVERT() .

As already mentioned, the date in the database is not formatted.

So what you want to know is what date format the database accepts for data entry or query .

This in Microsoft SQL Server depends on a few factors:

• What is the installation language for the database.
• What is the language of the database user you are using to connect.
• What date format was specified (if specified) in the session with the SET DATEFORMAT

If you install the server in a Portuguese Windows, the default language of the database will also be in Portuguese. Soon, all new users will default to the Portuguese language and then the date format accepted by the database will be dd / MM / yyyy and the query below will have the desired effect:

select * from tabela where data = '15/04/2015'

The problem is that when your software arrives on a client that installed SQL Server on an English Windows, this query will fail because the date format accepted by the database will be the US MM / dd / yyyy .

As much as worse, if in the query you wrote 12/04/2015 , instead of getting a mistake you would get invalid data, which is much more dangerous.

Solving the problem

Fortunately, MS SQL Server supports another date format (ISO standard) that does not depend on any configuration, which are:

YYYY-MM-DDThh:mm:ss[.mmm]

YYYYMMDD[ hh:mm:ss[.mmm]]

These formats are independent of the SET DATEFORMAT command.

So, for the above query to always work, you can write it like this:

select * from tabela where data = '20150415'

A better way to solve the problem

If you program an application layer, for example C # or Java or PHP ... It is better to pass the date to the database as a query parameter instead of concatenating the date as a string.

Doing this, in addition to not having to worry about date format you can still benefit from better performance when the query is re-run with different dates.

Read more about Microsoft SQL Server date formats: Datetime (Transact-SQL) .

The date format that SQLServer uses by default depends on the configured language.

You can find out what this format is by using the following command:

select dateformat from syslanguages where name = @@LANGUAGE  

The format used during a session can be changed using SET LANGUAGE a> or SET DATEFORMAT :

SET LANGUAGE { [ N ] 'language' | @language_var } 
SET DATEFORMAT { format | @format_var }