How to know which DATETIME format is used in a given column of SQL Server?

Question

I have a table on SQL Server and one of the camps was created as DATETIME. How should I know query or SQL Server Studio, what format default used of this DATETIME in my SQL Server?

Answer 1

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

In a column of the kind DateTime there is a date. Point.

If you want in some format, you should take the date and create a formatted text as you wish. But a formatted text is different from a date. The software doesn’t care about formats, this is used to show humans. And what is shown to humans is always a text, even if this text represents a date. But that’s it, a representation.

A way to get a formatted output would be like this:

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

I put in the Github for future reference.

Documentation of CONVERT().

Answer 2

As already said, the date in the database has no format.

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 some factors:

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

If you install the server on a Windows in English, the default language of the database will also be in English. Soon, all new users will have by default the Portuguese language and then the date format accepted by the bank 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 in a client that installed SQL Server on a Windows in English, this query will fail because the date format accepted by the bank will be the American, MM/dd/yyyy.

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

Solving the problem

Fortunately MS SQL Server accepts another date format (ISO standard) that is independent of any configuration, which are these:

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

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

These formats are even independent of the command SET DATEFORMAT.

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.

In doing so, in addition to not having to worry about date format you can still benefit from better performance when the query is rescheduled with different dates.

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

Answer 3

The date format that Sqlserver uses by default depends on the language set.

You can know what this format is using the following command:

select dateformat from syslanguages where name = @@LANGUAGE  

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

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

Answer 4

Use the command below:

dbcc useroptions;

The'DATEFORMAT 'field shows the default format.