Formatação padrão de data no SQL Server

Dando uma olhada nas estatísticas do blog percebi que a maioria das visitas ao blog é resultado de buscas por termos relacionados à formatação de data padrão do SQL Server. O interessante é que não havia abordado esse tema aqui ainda. Então vamos lá!

Deve-se ter em mente que o SQL Server armazena um valor do tipo Date no banco sem nenhum tipo de formatação e que o formato exibido é definido pela aplicação.

Em .NET C# é possível a configuração desse formato de data usando o método ToString junto com o formato desejado ou CultureInfo. Veja o exemplo em C# abaixo.

using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Globalization;
    using System.Threading;

    class Program
    {
        static void Main(string[] args)
        {
            DateTime hoje = DateTime.Now;
            
            foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures))
            {
                if(!ci.IsNeutralCulture)
                    Console.WriteLine("Formato {0} : {1} ---> " + hoje.ToString(new CultureInfo(ci.Name)), ci.Name, ci.DisplayName);
            }
            Console.ReadKey();
        }
    }

Saída:

Mesmo não encontrando nenhuma documentação confirmando, pelo que notei o SQL Server Management Studio utiliza o padrão ODBC canonical (yyyy-mm-dd hh:mi:ss.mmm(24h)) e até o momento não é possível alteração dessa configuração padrão.

Date -> Varchar

Caso você precise formatar o output de um campo date para um formato especifico você pode utilizar a função CONVERT. Vale lembrar que valor é convertido para varchar e a formatação é predefinida e aplicada conforme o style passado para a função.

select getdate() [SSMS Default]
select CONVERT(varchar(20), getdate(), 103) [dd/mm/yyyy]
select CONVERT(varchar(20), getdate(), 101) [mm/dd/yyyy]
select CONVERT(varchar(20), getdate(), 100) [mon dd yyyy hh:miAM (or PM)]

Resultado:

O SQL Server 2012 possui uma nova função para formatação, esta é mais flexível. A FORMAT permite informar a mascara desejada como parâmetro. Veja alguns exemplos.

select format(getdate(), 'dd.MM.yyyy hh:mm:ss') as [customFormat]
select format(getdate(), 'dd.MMMM.yyyy hh:mm:ss') as [customFormat]
select format(getdate(), 'dd.MMMM.yyyy hh:mm:ss', 'pt-br') as [customFormat]
select format(getdate(), 'dddd, dd "de" MMMM "de" yyyy', 'pt-br') as [customFormat]

Resultado:

Varchar -> Date

O formato padrão que o SQL Server irá utilizar ao tentar realizar casting de um valor varchar para um date é definido pela language utilizada na configuração da instancia. Esse valor pode ser sobrescrito pela language associado a um login ou por configuração em nível de sessão.
Você pode verificar os valores configurados usando os comandos abaixo.

select * from sys.configurations where name like '%language%'

select * from sys.server_principals where default_language_name is not null

select @@LANGUAGE

Resultado:

Veja no exemplo a seguir como a language pode interferir no casting do varchar para date. Repare que ao alterar a language o casting resultado em valores diferentes para mês e dia.

SET LANGUAGE 'Brazilian'
select @@LANGUAGE
SELECT CAST('06/03/2013' AS DATE) [FULLDATE], MONTH(CAST('06/03/2013' AS DATE)) [MONTH], DAY(CAST('06/03/2013' AS DATE)) [DAY]

SET LANGUAGE 'English'
select @@LANGUAGE
SELECT CAST('06/03/2013' AS DATE) [FULLDATE], MONTH(CAST('06/03/2013' AS DATE)) [MONTH], DAY(CAST('06/03/2013' AS DATE)) [DAY]

Resultado:

Para não ter que se preocupar com a language você pode utilizar os formatos ISO (YYYYMMDD) e ISO8601 (YYYY-MM-DDTHH:MM:SS:MMM). Veja abaixo.

SET LANGUAGE 'English'
select @@LANGUAGE
SELECT CAST('20130603' AS DATE) [FULLDATE], MONTH(CAST('20130603' AS DATE)) [MONTH], DAY(CAST('20130603' AS DATE)) [DAY]

SET LANGUAGE 'Brazilian'
select @@LANGUAGE
SELECT CAST('20130603' AS DATE) [FULLDATE], MONTH(CAST('20130603' AS DATE)) [MONTH], DAY(CAST('20130603' AS DATE)) [DAY]

Resultado:

Ou usando a função CONVERT.

select CONVERT(date, '06/03/2013', 103) [FULLDATE], day(CONVERT(date, '06/03/2013', 103)) [DAY], month(CONVERT(date, '06/03/2013', 103)) [MONTH]

select CONVERT(date, '03/06/2013', 101) [FULLDATE], day(CONVERT(date, '03/06/2013', 101)) [DAY], month(CONVERT(date, '03/06/2013', 101)) [MONTH]

Resultado:

Alguns links com mais informações
SQL SERVER – Denali – String Function – FORMAT() – A Quick Introduction
SQL Server Date Formats
The ultimate guide to the datetime datatypes

edit: Finalmente adicionado as imagens para ilustração 😛