Partilhar via


Usando dados de data e hora

As seções a seguir deste tópico fornecem informações e exemplos do uso de tipos de dados e funções de data e hora. Para obter uma visão geral de todos os tipos de dados e funções de data e hora do Transact-SQL, consulte Funções Date e Time (Transact-SQL).

  • Usando os operadores com tipos de dados de data e hora

  • Usando formatos de data e hora

  • Formatos de data e hora de literal de cadeia de caracteres

  • Formato de cadeia de caracteres não separado

  • Formato ISO 8601

  • Formatos de data alfabéticos

  • Formatos de data numéricos

  • Formatos de hora

  • Formato de data/hora ODBC

  • Convertendo data, hora, datetime2 e datetimeoffset

  • Conversão entre literais de cadeia de caracteres e hora(n), data, datetime2(n) e datetimeoffset(n)

  • Conversão entre tipos de dados de data e hora

  • Usando CAST e CONVERT com hora, data, datetime2 e datetimeoffset

  • Argumentos de estilo para o Functionq CONVERT

  • Limitações de data e hora do SQL Server

  • Compatibilidade com versões anteriores de clientes de nível inferior

  • Exemplos

Usando operadores com tipos de dados de data e hora

Os operadores relacionais (<, <=, >, >=, <>), os operadores de comparação (=, <, <=, >, >=, <>, !<, !>), os operadores lógicos e os predicados Booleanos (IS NULL, IS NOT NULL, IN, BETWEEN, EXISTS, NOT EXISTS e LIKE) têm suporte para todos os tipos de dados de data e hora.

Operadores aritméticos de data e hora

Para adicionar e subtrair todos os tipos de dados de data e hora, use DATEADD e DATEDIFF.

Usando formatos de data e hora

Os formatos de literal de cadeia de caracteres afetam a apresentação de dados em aplicativos para usuários, mas não o formato de armazenamento de inteiro subjacente no SQL Server. Entretanto, o SQL Server pode interpretar um valor de data em um formato de literal de cadeia de caracteres, inserido por um aplicativo ou usuário para armazenamento ou para uma função de data, como datas diferentes. A interpretação depende da combinação de configurações de formato de literal de cadeia de caracteres, tipo de dados e tempo de execução SET DATEFORMAT, SET LANGUAGE e opção default language.

Alguns formatos de literal de cadeia de caracteres não são afetados por essas configurações. Considere o uso de um formato que não depende dessas configurações, a menos que você saiba que as configurações estão corretas para o formato. O formato ISO 8601 não depende dessas configurações e é um padrão internacional. O Transact-SQL que usa formatos de literal de cadeia de caracteres, dependentes de configurações do sistema, é menos portátil.

Para localizar o formato de literal de cadeia de caracteres padrão para clientes de nível inferior, consulte o tópico de cada tipo de dados de data e hora. Para obter uma visão geral de todos os tipos de dados e funções de data e hora do Transact-SQL, consulte Funções Date e Time (Transact-SQL).

Não há suporte para o formato de data ydm para os tipos date, datetime2 e datetimeoffset. Ocorrerá um erro de tempo de execução.

Formatos de data e hora de literal de cadeia de caracteres

A tabela a seguir lista diferentes formatos de cadeia de caracteres de data e hora. Recomendamos que você use formatos da data e hora que não sejam dependentes de DATEFORMAT e sejam multilíngue. Os formatos ISO 8601, ‘1998-02-23T14:23:05’ e ‘1998-02-23T14:23:05-08:00’ são os únicos que estão em um padrão internacional. Eles não são DATEFORMAT ou dependentes de idioma de login padrão e são multilíngues.

Parte da data e hora

Tipo de formato

Exemplo de formato

Pode ser combinado com outros formatos

Dependente de DATEFORMAT

Multilíngue

Data

Não separado

ISO 8601

'19980223'

Sim

Nenhum

Sim

Data

Numérico

'02/23/1998'

Sim

Sim

Nenhum

(DATEFORMAT)

Data

ISO 8601 numérico

'1998-02-23'

Sim

Nenhum

Nenhum

Data

Alfabético

'23 de fevereiro de 1998'

Sim

Nenhum

Nenhum

(mês ou mês curto)

Data

Data ODBC

{d '1998-02-23'}

Nenhum

Nenhum

Sim

Hora

Hora ISO 8601

'14:23:05'

'10:00:00.123456'

Sim

Nenhum

Sim

Hora

Hora ODBC

{t '14:23:05'}

Nenhum

Nenhum

Sim

Data/hora

Data/hora ODBC

{ts '1998-02-23 14:23:05'}

Nenhum

Nenhum

Sim

Data/hora

ISO 8601

‘1998-02-23T14: 23:05’

‘1998-02-23T14: 23:05 -08:00’

Nenhum

Nenhum

Sim date, datetime2, datetimeoffset.

Data/hora

Padrão ANSI SQL

'1998-02-23 14:23:05'

'1998-02-23 14:23:05 -08:00'

Nenhum

Não (datetime2, datetimeoffset)

Sim (datetime)

Sim date, datetime2, datetimeoffset.

Data/hora

Combinação de data e hora

'19980223 14:23:05'

'02/23/1998 2:23:05 PM'

'1998-02-23 10:00:00.123'

'23 de fevereiro de 1998 14:23:05'

Nenhum

Sim

(parte de data)

Nenhum

Fuso horário

Formato de fuso horário

'+12:00'

'01:00'

'-08:00'

'Z'

Sim

Nenhum

Sim

As instruções a seguir mostram os efeitos das configurações SET LANGUAGE e SET DATEFORMAT.

DECLARE @Today date = '12/1/2003';
DECLARE @MyTime time = '12:30:22';
DECLARE @MyDatetimeoffset datetimeoffset = '12/1/2003 12:30:22 -5:00';
SET LANGUAGE Italian
    -- Returns: Changed language setting to Italiano.
SELECT DATENAME(month, @Today) AS 'Month Name';
    -- Returns: dicembre
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
    -- Returns: dic  1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
    -- Returns: 12

SET LANGUAGE us_english;
    -- Returns: Changed language setting to us_english.
SELECT DATENAME(month, @Today) AS 'Month Name';
    -- Returns: December
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
    -- Returns: Dec  1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
    -- Returns: 12
GO
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
    -- Returns: 1998-12-31
GO
-- Set date format to year, day, month. 
SET DATEFORMAT ydm;
GO
DECLARE @datevar datetimeoffset = '1998/31/12 12:30:22 -05:00';
SELECT @datevar AS DateVar;
    -- Returns: Msg 241, Conversion failed when converting 
    -- date and/or time from character string.
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
    -- Returns: 1998-12-31
GO

Formato de cadeia de caracteres não separado

Você pode especificar dados de data como uma cadeia de caracteres não separada. Os dados de data podem ser especificados usando quatro, seis ou oito dígitos, uma cadeia de caracteres vazia ou um valor de hora sem um valor de data.

A configuração de sessão SET DATEFORMAT não se aplica a entradas de data totalmente numéricas, como entradas numéricas sem separadores. As cadeias de caracteres de seis e oito dígitos são sempre interpretadas como ymd. O mês e o dia sempre devem ser de dois dígitos.

Este é o formato válido de cadeia de caracteres não separada: [19]960415

Uma cadeia de caracteres de apenas quatro dígitos é interpretada como o ano. O mês e a data são definidos como Janeiro 1. Ao especificar apenas quatro dígitos, você deve incluir o século.

Formato ISO 8601

A data ISO 8601 com formato de hora é a seguinte:

  • AAAA-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]

  • YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, Tempo Universal Coordenado)

O T indica o início da parte de hora do valor da data/hora.

Para usar o formato ISO 8601, você deve especificar cada elemento no formato. Isso inclui o T, os dois-pontos (:), o + ou -, e os pontos (.). Os parênteses indicam que os componentes de frações de segundo ou as diferenças de fuso horário são opcionais.

O componente de hora é especificado no formato de 24 horas.

A vantagem de usar o formato ISO 8601 é que ele é um padrão internacional. Os valores de data e hora especificados com esse formato são inequívocos. Esse formato não é afetado por SET DATEFORMAT, SET LANGUAGE, das configurações de idioma padrão de login.

A seguir estão dois exemplos de valores de data/hora especificados no formato ISO 8601:

  • 2004-05-23T14: 25:10

  • 2004-05-23T14:25:10.1234567+07:00

Formatos de data alfabéticos

Você pode especificar um mês como um nome, por exemplo, April ou a abreviação Apr em inglês. Eles deveriam ser especificados na configuração LANGUAGE da sessão, por exemplo, avril ou avr em francês. As vírgulas são opcionais e não há diferenciação entre letras maiúsculas e minúsculas.

Aqui estão algumas diretrizes para o uso de formatos de data alfabéticos:

  • Insira os dados de data e hora com aspas simples (').

  • Se você especificar apenas os dois últimos dígitos do ano, os valores menores que os dois últimos dígitos do valor da opção de configuração two digit year cutoff estarão no mesmo século que o ano de corte. Os valores maiores ou iguais ao valor dessa opção estarão no século anterior ao ano de corte. Por exemplo, se two digit year cutoff for 2050 (padrão), 25 será interpretado como 2025 e 50 será interpretado como 1950. Para evitar ambiguidade, use anos de quatro dígitos.

  • Se o dia estiver ausente, o primeiro dia do mês será fornecido.

  • A configuração de sessão SET DATEFORMAT não é aplicada quando você especifica o mês de forma alfabética.

  • Os formatos a seguir são os formatos alfabéticos válidos para dados de data do SQL Server. Os caracteres entre parênteses são opcionais. 

  • Apr[il] [15][,] 1996

  • Apr[il] 15[,] [19]96

  • Apr[il] 1996 [15]

  • [15] Apr[il][,] 1996

  • 15 Apr[il][,][19]96

  • 15 [19]96 apr[il]

  • [15] 1996 apr[il]

  • 1996 APR[IL] [15]

  • 1996 [15] APR[IL]

Formatos de data numéricos

Você pode especificar dados de data com um mês numérico. Por exemplo, 5/20/97 representa o vigésimo dia de maio de 1997. Ao usar um formato de data numérico, especifique o ano, o mês e o dia em uma cadeia de caracteres com barras (/), hifens (-) ou pontos (.) como separadores. Essa cadeia de caracteres deve ser exibida da seguinte forma:

número separador número separador número [hora] [hora]

Os seguintes formatos numéricos são válidos:

  • [0]4/15/[19]96--(mda)

  • [0]4-15-[19]96 -- (mda)

  • [0]4.15.[19]96 -- (mda)

  • [0]4/[19]96/15 -- (mad)

  • 15/[0]4/[19]96 -- (dma)

  • 15/[19]96/[0]4 -- (dam)

  • [19]96/15/[0]4 -- (adm)

  • [19]96/[0]4/15 -- (amd)

O idioma padrão DATEFORMAT de uma sessão é definido pelo idioma padrão do login, uma instrução SET LANGUAGE ou uma instrução SET DATEFORMAT. Quando o idioma é definido como us_english pelo logon padrão ou pela instrução SET LANGUAGE, a ordem padrão da data é mdy.

Você pode alterar a ordem da data usando a instrução SET DATEFORMAT. A configuração de SET DATEFORMAT determina como são interpretados os valores de data. Se a ordem não corresponder à configuração, os valores não serão interpretados como datas, porque eles estarão fora do intervalo ou serão mal interpretados. Por exemplo, 12/10/08 pode ser interpretada como uma de seis datas, dependendo da configuração DATEFORMAT. Um ano de quatro dígitos será interpretado como ano.

Formatos de hora

O SQL Server reconhece os formatos de dados de hora a seguir. Insira cada formato com aspas simples (').

  • 14:30

  • 14:30[:20:999]

  • 14:30[:20.9]

  • 4am

  • 4 PM

  • [0]4[:30:20:500]AM

As instruções a seguir mostram os valores retornados de diferentes entradas da função CAST.

SELECT CAST('01/01/2000 14:30' AS datetime2)
    --Returns: 2000-01-01 14:30:00.0000000
SELECT CAST('01/01/2000 14:30:20:999' AS datetime2)
    -- Returns: 2000-01-01 14:30:20.9990000
SELECT CAST('01/01/2000 14:30:20.9' AS datetime2)
    -- Returns: 2000-01-01 14:30:20.9000000
SELECT CAST('01/01/2000 4am' AS datetime2)
    -- Returns: 2000-01-01 04:00:00.0000000
SELECT CAST('01/01/2000 4 PM' AS datetime2)
    -- Returns: 2000-01-01 16:00:00.0000000
SELECT CAST('01/01/2000 04:30:20:500AM' AS datetime2)
    -- Returns: 2000-01-01 04:30:20.5000000
SELECT CAST('01/01/2000 04:30:20:500 AM' AS datetime2)
    -- Returns: 2000-01-01 04:30:20.5000000
GO

Você pode especificar um sufixo AM ou PM para indicar se o valor de hora está antes ou depois do meio-dia. Não é feita distinção entre letras maiúsculas e minúsculas de AM ou PM.

As horas podem ser especificadas usando o relógio de 12 horas ou de 24 horas. Os valores de hora são interpretados da maneira a seguir.

  • O valor de hora 00 representa a hora depois da meia-noite (AM), independentemente de você ter especificado AM. Não é possível especificar PM quando o valor é igual a 00.

  • Os valores de hora de 01 a 11 representam as horas antes do meio-dia se não for especificado AM nem PM. Eles também representam as horas antes do meio-dia quando AM é especificado. Eles representarão horas depois do meio-dia se PM for especificado.

  • O valor de hora 12 representa a hora que começa ao meio-dia, se não for especificado AM nem PM. Se AM for especificado, ele representará a hora que começa à meia-noite. Se PM for especificado, ele representará a hora que começa ao meio-dia. Por exemplo: 12:01 é 1 minuto depois do meio-dia, como 12:01 PM, enquanto 12:01 AM é 1 minuto depois da meia-noite. Especificar 12:01 AM é igual a especificar 00:01 ou 00:01 AM.

  • Os valores de hora de 13 a 23 representam as horas após o meio-dia se não for especificado AM nem PM. Eles também representam as horas após o meio-dia quando PM é especificado. Você não pode especificar AM quando o valor de hora é de 13 a 23.

  • Um valor de hora 24 não é válido; use 12:00 AM ou 00:00 para representar meia-noite.

Os milissegundos podem ser precedidos por ou dois-pontos (:) ou ponto (.). Se precedido por dois-pontos, o número significa milésimos de segundo. Se precedido por um ponto, um único dígito significa décimos de segundo, dois dígitos significa centésimos de segundo e três dígitos, milésimos de segundo. Por exemplo, 12:30:20:1 indica vinte e um milésimos de segundo após 12:30; 12:30:20.1 indica vinte e um décimos de segundo após 12:30.

Formato de data/hora ODBC

O API ODBC define sequências de escape para representar valores de data e hora que o ODBC chama de dados de carimbo de data/hora. Também há suporte desse formato de carimbo de data/hora de ODBC pela definição de idioma OLE DB (DBGUID-SQL) e o provedor OLE DB Microsoft para SQL Server. Os aplicativos que usam ADO, OLE DB e as APIs baseadas em ODBC podem usar esse formato de carimbo de data/hora de ODBC para representar datas e horas.

O SQL Server sempre trata os dados de ODBC como sendo do tipo de dados datetime.

As sequências de escape de carimbo de data/hora são do formato:

{ literal_type 'constant_value' }

  • literal_type
    Especifica o tipo da sequência de escape. A seguir estão os argumentos válidos para literal_type.

    d = somente data

    t = somente hora

    ts = carimbo de data/hora (hora + data)

  • 'constant_value'
    É o valor da sequência de escape. O constant_value deve seguir esses formatos para cada literal_type.

    literal_type

    formato constant_value

    d

    AAAA-MM-DD

    t

    hh:mm:ss[.fff]

    ts

    AAAA-MM-DD mm.:ss[.fff]

Estes são exemplos de constantes de hora e data de ODBC:

  • { ts '1998-05-02 01:23:56.123' }

  • { d '1990-10-02' }

  • { t '13:33:41' }

Não confunda o nome do tipo de dados de carimbo de data/hora de ODBC e OLE DB com o nome do tipo de dados de Transact-SQLcarimbo de data/hora. O tipo de dados de carimbo de data/hora de ODBC e OLE DB registra datas e horas. O tipo de dados Transact-SQLtimestamp é um tipo de dados binário que não tem nenhum valor relacionado à hora.

Convertendo data, hora, datetime2 e datetimeoffset

Há dois tipos de conversões entre tipos de dados diferentes: explícito e implícito. As conversões implícitas acontecem sem usar as funções CAST ou CONVERT. As conversões explícitas exigem o uso das funções CAST ou CONVERT.

Conversão entre literais de cadeia de caracteres e hora(n), data, datetime2 (n) e datetimeoffset(n)

Serão permitidas conversões de literais de cadeia de caracteres para tipos de data e hora se todas as partes da cadeia de caracteres estiverem em formatos válidos. Caso contrário, será gerado um erro de tempo de execução.

As conversões implícitas ou explícitas que não especificam um estilo, de tipos de data e hora em literais de cadeia de caracteres estarão no formato padrão da sessão atual.

Para conversão implícita de tipos de dados de data, hora, datetime2 e datetimeoffset em cadeias de caracteres, o formato padrão de SQL AAA-MM-DD hh:mi:ss.[nnnnnnn]. estilo CONVERT 121, será aplicado. O estilo CONVERT formato 0, mês dd aaaa hh:miAM (ou PM), é aplicado a tipos de dados datetime e smalldatetime.

A tabela a seguir mostra as regras para conversão entre tipos de date, time, datetime2 e datetimeoffset e literais de cadeia de caracteres.

Literal de cadeia de caracteres de entrada

date

time(n)

datetime2(n)

datetimeoffset(n)

ODBC DATE

Consulte a observação 1.

Consulte a observação 1.

Consulte a observação 1.

Consulte a observação 1.

ODBC TIME

Consulte a observação 1.

Consulte a observação 1.

Consulte a observação 1.

Consulte a observação 1.

ODBC DATETIME

Consulte a observação 1.

Consulte a observação 1.

Consulte a observação 1.

Consulte a observação 1.

Apenas DATE

Trivial

Os valores padrão são fornecidos

A parte TIME assume 00:00:00 como padrão.

A parte TIME assume 00:00:00 como padrão. TIMEZONE assume +00:00 como padrão.

Apenas TIME

Os valores padrão são fornecidos

Trivial

A parte DATE assume 1900-1-1 como padrão.

A parte DATE assume 1900-1-1 como padrão. TIMEZONE assumirá +00:00 como padrão.

Apenas TIMEZONE

Os valores padrão são fornecidos

Os valores padrão são fornecidos

Os valores padrão são fornecidos

Os valores padrão são fornecidos

DATE + TIME

A parte DATE da cadeia de caracteres de entrada é usada.

A parte TIME da cadeia de caracteres de entrada é usada.

Trivial

TIMEZONE assume +00: 00 como padrão.

DATE + TIMEZONE

Não permitido

Não permitido

Não permitido

Não permitido

TIME + TIMEZONE

Os valores padrão são fornecidos

A parte TIME da cadeia de caracteres de entrada é usada.

A parte DATE assume 1900-1-1 como padrão. A entrada TIMEZONE é ignorada.

A parte DATE assume 1900-1-1 como padrão.

DATE + TIME + TIMEZONE

A parte DATE de DATETIME local será usada.

A parte TIME de DATETIME local será usada.

O DATETIME local será usado.

Trivial

Observações sobre conversão

  1. Os literais de cadeia de caracteres de ODBC são mapeados para o tipo de dados datetime. Qualquer operação de atribuição de literais de ODBC DATETIME em tipos de date, time, datetime2 ou datetimeoffset causará uma conversão implícita entre datetime e esses tipos, conforme definido pelas regras de conversão.

  2. A precisão de frações de segundo de datetime tem uma exatidão de três centésimos de segundo (equivalente a 3,33 milissegundos ou 0,00333 segundos). Os valores são arredondados em incrementos de .000, .003 ou .007 segundos. ‘08/22/1995 10:15:19:999' é arredondado porque ‘.999' excede a precisão.

  3. Para time(3), datetime2(3) ou datetimeoffset(3), as frações de segundo têm uma exatidão de um milissegundo. Portanto, ‘1995-8-22 10:15:19:999' não será arredondado.

  4. A parte da diferença de TIMEZONE de entrada deve ser sempre de dois dígitos para hh e mm. O sinal, + ou -, é obrigatório.

Conversão entre tipos de dados de data e hora

As tabelas nesta seção descrevem como cada um dos tipos de dados de data e hora é convertido em outros tipos de dados de data e hora:

  • date

  • time(n)

  • datetime

  • smalldatetime

  • datetimeoffset(n)

  • datetime2

Tipo de dados de data

A tabela a seguir descreve o que ocorre quando um tipo de dados de date é convertido em outros tipos de dados de data e hora.

Tipo de dados para converter em

Detalhes da conversão

time(n)

A conversão falha e a mensagem de erro 206 é gerada: "Conflito no tipo de operando: a data é incompatível com a hora".

datetime

A data é copiada. O código a seguir mostra os resultados da conversão de um valor date em um valor datetime.

DECLARE @date date;
DECLARE @datetime datetime;
SET @date = '12-10-25';
SET @datetime = @date;
SELECT @date AS '@date', @datetime AS '@datetime';
--Result
--@date      @datetime
------------ -----------------------
--2025-12-10 2025-12-10 00:00:00.000
--
--(1 row(s) affected)

smalldatetime

Quando o valor date está no intervalo de uma smalldatetime, o componente de data é copiado e o componente de hora é definido como 00:00:00.000. Quando o valor date está fora do intervalo de um valor smalldatetime, a mensagem de erro 242 é gerada: "A conversão de um tipo de dados de data em um tipo de dados smalldatetime resultou em um valor fora do intervalo."; e o valor smalldatetime é definido como NULL.

O código a seguir mostra os resultados da conversão de um valor date em um valor smalldatetime.

DECLARE @date date;
DECLARE @smalldatetime smalldatetime;
SET @date = '1912-10-25';
SET @smalldatetime = @date;
SELECT @date AS '@date', @smalldatetime AS '@smalldatetime';
--Result
--@date      @smalldatetime
------------ -----------------------
--1912-10-25 1912-10-25 00:00:00
--
--(1 row(s) affected)

datetimeoffset(n)

A data é copiada e a hora é definida como 00:00.0000000 +00:00.

O código a seguir mostra os resultados da conversão de um valor date em um valor datetimeoffset(3).

DECLARE @date date;
DECLARE @datetimeoffset datetimeoffset(3);
SET @date = '1912-10-25';
SET @datetimeoffset = @date;
SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset';
--Result
--@date      @datetimeoffset
------------ ------------------------------
--1912-10-25 1912-10-25 00:00:00.000 +00:00
--
--(1 row(s) affected)

datetime2(n)

O componente de data é copiado e o componente de hora é definido como 00:00.000000.

O código a seguir mostra os resultados da conversão de um valor date em um valor datetime2(3).

DECLARE @date dat;e
DECLARE @datetime2 datetime2(3);
SET @date = '1912-10-25';
SET @datetime2 = @date;
SELECT @date AS '@date', @datetime2 AS '@datetime2(3)';
--Result
--@date      @datetime2(3)
------------ -----------------------
--1912-10-25 1912-10-25 00:00:00.000
--
--(1 row(s) affected)

Tipo de dados de hora(n)

A tabela a seguir descreve o que ocorre quando um tipo de dados de time é convertido em outros tipos de dados de data e hora.

Tipo de dados para converter em

Detalhes da conversão

time(n)

A hora, o minuto e os segundos são copiados. Quando a precisão de destino é menor do que a precisão de origem, as frações de segundo são truncadas para se ajustarem à precisão de destino.

O exemplo a seguir mostra os resultados da conversão de um valor time(4) em um valor time(3).

DECLARE @timeTo time(3);
DECLARE @timeFrom time(4);
SET @timeFrom = '12:34:54.1234';
SET @timeTo = @timeFrom;
SELECT @timeTo AS 'time(3)', @timeFrom AS 'time(4)';
--Results
--time(3)      time(4)
-------------- -------------
--12:34:54.123 12:34:54.1234
--
--(1 row(s) affected)

date

A conversão falha e a mensagem de erro 206 é gerada: "Conflito no tipo de operando: a data é incompatível com a hora".

datetime

Os valores de hora, minuto e segundo são copiados e o componente de data é definido como '1900-01-01'. Quando a precisão de frações de segundos do valor time(n) é maior do que três dígitos, o resultado datetime é truncado.

O código a seguir mostra os resultados da conversão de um valor time(4) em um valor datetime.

DECLARE @time time(4);
DECLARE @datetime datetime;
SET @time = '12:15:04.1234';
SET @datetime = @time;
SELECT @time AS '@time', @datetime AS '@datetime';
--Result
--@time         @datetime
--------------- -----------------------
--12:15:04.1234 1900-01-01 12:15:04.123
--
--(1 row(s) affected)

smalldatetime

A data é definida como '1900-01-01' e os valores de hora e minuto são copiados. Os segundos e as frações de segundo são definidos como 0.

O código a seguir mostra os resultados da conversão de um valor time(4) em um valor smalldatetime.

DECLARE @time time(4);
DECLARE @smalldatetime smalldatetime;
SET @time = '12:15:04.1234';
SET @smalldatetime = @time;
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';
--Result
--@time         @smalldatetime
--------------- -----------------------
--12:15:04.1234 1900-01-01 12:15:00
--
--(1 row(s) affected)

datetimeoffset(n)

A data é definida como '1900-01-01' e a hora é copiada. O deslocamento de fuso horário é definido como +00:00. Quando a precisão de frações de segundo do valor time(n) é maior do que datetimeoffset(n), o valor é truncado para ser ajustado.

O exemplo a seguir mostra os resultados da conversão de um valor time(4) em um tipo de valor datetimeoffset(3).

DECLARE @time time(4);
DECLARE @datetimeoffset datetimeoffset(3);
SET @time = '12:15:04.1234';
SET @datetimeoffset = @time;
SELECT @time AS '@time', @datetimeoffset AS '@datetimeoffset';
--Result
--@time         @datetimeoffset
--------------- ------------------------------
--12:15:04.1234 1900-01-01 12:15:04.123 +00:00
--
--(1 row(s) affected)

datetime2(n)

A data é definida como '1900-01-01', o componente de hora é copiado e o deslocamento de fuso horário é definido como 00:00. Quando a precisão de frações de segundo do valor datetime2(n) é maior do que time(n), o valor é truncado para ser ajustado.

O exemplo a seguir mostra os resultados da conversão de um valor time(4) em um valor datetime2(2).

DECLARE @time time(4);
DECLARE @datetime2 datetime2(3);
SET @time = '12:15:04.1234';
SET @datetime2 = @time;
SELECT @datetime2 AS '@datetime2', @time AS '@time';
--Result
--@datetime2              @time
------------------------- -------------
--1900-01-01 12:15:04.123 12:15:04.1234
--
--(1 row(s) affected)

Tipo de dados datetime

A tabela a seguir descreve o que ocorre quando um tipo de dados datetime é convertido em outros tipos de dados de data e hora.

Tipo de dados para converter em

Detalhes da conversão

date

O ano, o mês e o dia são copiados. O componente de hora é definido como 00:00:00.000.

O código a seguir mostra os resultados da conversão de um valor date em um valor datetime.

DECLARE @date date;
DECLARE @datetime datetime;
SET @date = '12-21-05';
SET @datetime = @date;
SELECT @datetime AS '@datetime', @date AS '@date';
--Result
--@datetime               @date
------------------------- ----------
--2005-12-21 00:00:00.000 2005-12-21

time(n)

O componente de hora é copiado e o componente de data é definido como '1900-01-01'. Quando a precisão fracionária do valor time(n) é maior do que três dígitos, o valor é truncado para ser ajustado.

O exemplo a seguir mostra os resultados da conversão de um valor time(4) em um valor datetime.

DECLARE @time time(4);
DECLARE @datetime datetime;
SET @time = '12:10:05.1234';
SET @datetime = @time;
SELECT @datetime AS '@datetime', @time AS '@time';
--Result
--@datetime               @time
------------------------- -------------
--1900-01-01 12:10:05.123 12:10:05.1234
--
--(1 row(s) affected)

smalldatetime

As horas e os minutos são copiados. Os segundos e as frações de segundo são definidos como 0.

O código a seguir mostra os resultados da conversão de um valor smalldatetime em um valor datetime.

DECLARE @smalldatetime smalldatetime;
DECLARE @datetime datetime;
SET @smalldatetime = '12-01-01 12:32';
SET @datetime = @smalldatetime;
SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';
--Result
--@datetime               @smalldatetime
------------------------- -----------------------
--2001-12-01 12:32:00.000 2001-12-01 12:32:00
--
--(1 row(s) affected)

datetimeoffset(n)

Os componentes de data e hora são copiados. O fuso horário é truncado. Quando a precisão fracionária do valor datetimeoffset(n) é maior do que três dígitos, o valor é truncado.

O exemplo a seguir mostra os resultados da conversão de um valor datetimeoffset(4) em um valor datetime.

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @datetime datetime;
SET @datetimeoffset = '1968-10-23 12:45:37.1234 +10:0';
SET @datetime = @datetimeoffset;
SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';
--Result
--@datetime               @datetimeoffset
------------------------- ------------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1234 +01:0
--
--(1 row(s) affected)

datetime2(n)

A data e a hora são copiadas. Quando a precisão fracionária do valor datetime2(n) é maior do que três dígitos, o valor é truncado.

O exemplo a seguir mostra os resultados da conversão de um valor datetime2(4) em um valor datetime.

DECLARE @datetime2 datetime2(4);
DECLARE @datetime datetime;
SET @datetime2 = '1968-10-23 12:45:37.1237';
SET @datetime = @datetime2;
SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';
--Result
--@datetime               @datetime2
------------------------- ------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237
--
--(1 row(s) affected)

Tipo de dados smalldatetime

A tabela a seguir descreve o que ocorre quando um tipo de dados smalldatetime é convertido em outros tipos de dados de data e hora.

Tipo de dados para converter em

Detalhes da conversão

date

O ano, o mês e o dia são copiados.

O código a seguir mostra os resultados da conversão de um valor smalldatetime em um valor date.

DECLARE @smalldatetime smalldatetime;
DECLARE @date dat;e
SET @smalldatetime = '1955-12-13 12:43:10';
SET @date = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @date AS 'date';
--Result
--@smalldatetime          date
------------------------- ----------
--1955-12-13 12:43:00     1955-12-13
--
--(1 row(s) affected)

time(n)

As horas, os minutos e os segundos são copiados. As frações de segundo são definidas como 0.

O código a seguir mostra os resultados da conversão de um valor smalldatetime em um valor time(4).

DECLARE @smalldatetime smalldatetime;
DECLARE @time time(4);
SET @smalldatetime = '1955-12-13 12:43:10';
SET @time = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @time AS 'time';
--Result
--@smalldatetime          time
------------------------- -------------
--1955-12-13 12:43:00     12:43:00.0000
--
--(1 row(s) affected)

datetime

O valor smalldatetime é copiado para o valor datetime. As frações de segundo são definidas como 0.

O código a seguir mostra os resultados da conversão de um valor smalldatetime em um valor datetime.

DECLARE @smalldatetime smalldatetime;
DECLARE @datetime datetime;
SET @smalldatetime = '1955-12-13 12:43:10';
SET @datetime = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime';
--Result
--@smalldatetime          datetime
------------------------- -----------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.000
--
--(1 row(s) affected)

datetimeoffset(n)

O valor smalldatetime é copiado para o valor datetimeoffset(n). Os segundos fracionários são definidos como 0 e o deslocamento de fuso horário é definido como +00:0.

O código a seguir mostra os resultados da conversão de um valor smalldatetime em um valor datetimeoffset(4).

DECLARE @smalldatetime smalldatetime;
DECLARE @datetimeoffset datetimeoffset(4);
SET @smalldatetime = '1955-12-13 12:43:10';
SET @datetimeoffset = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetimeoffset AS 'datetimeoffset(4)';
--Result
--@smalldatetime          datetimeoffset(4)
------------------------- ------------------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.0000 +00:0
--
--(1 row(s) affected)

datetime2(n)

O valor smalldatetime é copiado para o valor datetime2(n). As frações de segundo são definidas como 0.

O código a seguir mostra os resultados da conversão de um valor smalldatetime em um valor datetime2(4).

DECLARE @smalldatetime smalldatetime;
DECLARE @datetime2 datetime2(4);
SET @smalldatetime = '1955-12-13 12:43:10';
SET @datetime2 = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetime2 AS ' datetime2(4)';
--Result
--@smalldatetime           datetime2(4)
------------------------- ------------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.0000
--
--(1 row(s) affected)

Tipo de dados datetimeoffset(n)

A tabela a seguir descreve o que ocorre quando um tipo de dados datetimeoffset(n) é convertido em outros tipos de dados de data e hora.

Tipo de dados para converter em

Detalhes da conversão

date

O ano, o mês e o dia são copiados.

O código a seguir mostra os resultados da conversão de um valor datetimeoffset(4) em um valor date.

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @date date;
SET @datetimeoffset = '12-10-25 12:32:10 +01:0;
SET @date = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';
--Result
--@datetimeoffset                date
-------------------------------- ----------
--2025-12-10 12:32:10.0000 +01:0 2025-12-10
--
--(1 row(s) affected)

time(n)

A hora, o minuto, o segundo e as frações de segundo são copiados . O valor de fuso horário é truncado. Quando a precisão do valor datetimeoffset(n) é maior do que time(n), o valor é truncado.

O código a seguir mostra os resultados da conversão de um valor datetimeoffset(4) em um valor time(3).

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @time time(3);
SET @datetimeoffset = '12-10-25 12:32:10.1234 +01:0';
SET @time = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';
--Result
--@datetimeoffset                time
-------------------------------- ------------
--2025-12-10 12:32:10.1234 +01:0 12:32:10.123
--
--(1 row(s) affected)

datetime

Os valores de data e hora são copiados e o fuso horário é truncado. Quando a precisão fracionária do valor datetimeoffset(n) é maior do que três dígitos, o valor é truncado.

O código a seguir mostra os resultados da conversão de um valor datetimeoffset(4) em um valor datetime.

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @datetime datetime;
SET @datetimeoffset = '12-10-25 12:32:10.1234 +01:0';
SET @datetime = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';
--Result
--@datetimeoffset                datetime
-------------------------------- -----------------------
--2025-12-10 12:32:10.1234 +01:0 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

smalldatetime

A data, as horas e os minutos são copiados. Os segundos são definidos como 0.

O código a seguir mostra os resultados da conversão de um valor datetimeoffset(3) em um valor smalldatetime.

DECLARE @datetimeoffset datetimeoffset(3);
DECLARE @smalldatetime smalldatetime;
SET @datetimeoffset = '1912-10-25 12:24:32 +10:0';
SET @smalldatetime = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';
--Result
--@datetimeoffset                @smalldatetime
-------------------------------- -----------------------
--1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00
--
--(1 row(s) affected)

datetime2(n)

A data e a hora são copiadas ao valor datetime2 e o fuso horário é truncado. Quando a precisão do valor datetime2(n) é maior do que datetimeoffset(n), as frações de segundos são truncadas para serem ajustadas.

O código a seguir mostra os resultados da conversão de um valor datetimeoffset(4) em um valor datetime2(3).

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @datetime2 datetime2(3);
SET @datetimeoffset = '1912-10-25 12:24:32.1234 +10:0';
SET @datetime2 = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';
--Result
--@datetimeoffset                @datetime2
-------------------------------- -----------------------
--1912-10-25 12:24:32.1234 +10:0 1912-10-25 12:24:32.123
--
--(1 row(s) affected)

Tipo de dados datetime2

A tabela a seguir descreve o que ocorre quando um tipo de dados datetime2 é convertido em outros tipos de dados de data e hora.

Tipo de dados para converter em

Detalhes da conversão

date

O ano, o mês e o dia são copiados.

O código a seguir mostra os resultados da conversão de um valor datetime2(4) em um valor date.

DECLARE @datetime2 datetime2(4;
DECLARE @date date;
SET @datetime2 = '12-10-25 12:32:10.1234';
SET @date = @datetime2;
SELECT @datetime2 AS '@datetime2', @date AS 'date';
--Result
--@datetime2               date
-------------------------- ----------
--2025-12-10 12:32:10.1234 2025-12-10
--
--(1 row(s) affected)

time(n)

A hora, o minuto, o segundo e as frações de segundo são copiados .

O código a seguir mostra os resultados da conversão de um valor datetime2(4) em um valor time(3).

DECLARE @datetime2 datetime2(4);
DECLARE @time time(3);
SET @datetime2 = '12-10-25 12:32:10.1234';
SET @time = @datetime2;
SELECT @datetime2 AS '@datetime2', @time AS 'time(3)';
--Result
--@datetime2                time(3) 
-------------------------- ------------
--2025-12-10 12:32:10.1234 12:32:10.123
--
--(1 row(s) affected)

datetime

Os valores de data e hora são copiados. Quando a precisão fracionária do valor datetimeoffset(n) é maior do que três dígitos, o valor é truncado.

O código a seguir mostra os resultados da conversão de um valor datetime2 em um valor datetime.

DECLARE @datetime2 datetime;2
DECLARE @datetime datetime;
SET @datetime2 = '12-10-25 12:32:10.1234567;'
SET @datetime = @datetime2;
SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime';
--Result
--@datetime2                  @datetime
----------------------------- -----------------------
--2025-12-10 12:32:10.1234567 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

smalldatetime

A data, as horas e os minutos são copiados. Os segundos são definidos como 0.

O código a seguir mostra os resultados da conversão de um valor datetime2 em um valor smalldatetime.

DECLARE @datetime2 datetime2;
DECLARE @smalldatetime smalldatetime;
SET @datetime2 = '12-10-25 12:32:10.1234567';
SET @smalldatetime = @datetime2;
SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime';
--Result
--@datetime2                  @datetime
----------------------------- -----------------------
--2025-12-10 12:32:10.1234567 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

datetimeoffset(n)

O valor datetime2(n) é copiado para o valor datetimeoffset(n). O deslocamento de fuso horário é definido como +00:0. Quando a precisão do valor datetime2(n) é maior do que a precisão do valor datetimeoffset(n), o valor é truncado para ser ajustado.

O código a seguir mostra os resultados da conversão de um valor datetime2(5) em um valor datetimeoffset(3).

DECLARE @datetime2 datetime2(3);
DECLARE @datetimeoffset datetimeoffset(2);
SET @datetime2 = '12-10-25 12:32:10.1234567';
SET @datetimeoffset = @datetime2;
SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset(2)';
--Result
--@datetime2              @datetimeoffset(2)
------------------------- -----------------------------
--2025-12-10 12:32:10.123 2025-12-10 12:32:10.12 +00:00
--
--(1 row(s) affected)

Usando CAST e CONVERT com hora, data, datetime2 e datetimeoffset

Esta seção fornece informações sobre a conversão entre tipos de dados de data e hora.

Conversão em datetimeoffset

  • Sempre que um valor datetimeoffset com fuso horário deva derivar implicitamente de um valor sem fuso horário (por exemplo, em uma operação de atribuição simples), o valor sem fuso horário é tratado como local e a diferença de fuso horário padrão atual (00:00) é subtraída, resultando no UTC.
  1. O fuso horário UTC (00:00) é sempre anexado quando você converte tipos de dados que não são de fuso horário em datetimeoffset para as seguintes conversões:
  • date em datetimeoffset

  • time em datetimeoffset

  • datetime2 em datetimeoffset

  • datetime ou smalldatetime em datetimeoffset

  • Literais de cadeia de caracteres em date válida, time ou datetime sem formatos de fuso horário em datetimeoffset

Conversão de datetimeoffset

Ao converter de datetimeoffset nos seguintes tipos sem fuso horário, o estilo 0 (padrão) sempre indicará que o valor retornado date, time, datetime2, datetime ou smalltime está no formato local de diferença de fuso horário preservada e o estilo 1 sempre indica o formato UTC.

Sempre que um valor de data ou hora sem fuso horário for derivado implicitamente em uma das conversões a seguir, o valor datetimeoffset será tratado como UTC. A diferença de fuso horário preservada será adicionada ao valor para fornecer a hora local. O resultado, sem qualquer diferença de fuso horário é a hora local.

  • datetimeoffset em date

  • datetimeoffset em time

  • datetimeoffset em datetime2

  • datetimeoffset em datetime ou smalldatetime

  • Os estilos 0 e 1 não podem ser usados para datetimeoffset na conversão de cadeia de caracteres. Em vez disso, primeiro converta de datetimeoffset em datetime2 ou datetime, e depoisem varchar ou char.

Se um estilo CONVERT existente incluir a parte de hora, e a conversão for de datetimeoffset em cadeia de caracteres, a diferença de fuso horário (com exceção do estilo 127) será incluída. Se você não quiser a diferença de fuso horário, poderá primeiramente aplicar CAST a datetime2 e, depois, à cadeia de caracteres.

Todos os estilos de data e hora existentes serão aplicados a qualquer datetimeoffset na conversão de cadeia de caracteres e a diferença de fuso horário será mantida.

A parte da diferença de fuso horário da cadeia de caracteres de entrada deve ser sempre de dois dígitos para hh e mm e o sinal, + ou -, é obrigatório.

Informações descartadas na conversão

  • Quando você converte de datetime2 ou de datetimeoffset em date, não há arredondamento e a parte da data é extraída explicitamente. Para datetimeoffset, a extração é executada na data e hora locais, mas não o valor UTC.

  • Para qualquer conversão implícita de datetimeoffset em date, time, datetime2, datetime ou smalldatetime, a conversão é baseada no valor de data e hora local (para a diferença de fuso horário persistente). Por exemplo, quando o valor datetimeoffset(3), 2006-10-21 12:20:20.999 -8: 00, é convertido em time(3), o resultado é 12:20:20.999, não 20:20:20.999 (UTC).

Truncamento em conversão

  • As conversões de valores de hora de precisão mais alta em valores de precisão mais baixa são permitidas. Os valores da precisão mais alta serão truncados para se ajustarem à precisão mais baixa.

Conversão de frações de segundo

Se um estilo incluir o formato de hora hh:mm:ss.mmm, o formato se tornará hh:mm:ss. [nnnnnnn] para time(n), datetime2(n) e datetimeoffset(n). O número de dígitos depende da especificação do tipo. Se você quiser apenas a precisão de milissegundo, converta primeiro em datetime2(3) e, depois, em cadeia de caracteres.

Para estilos 9, 109, 13, 113, 21, 121, 130 e 131, não há suporte para o separador precedente de frações de segundo de dois-pontos (:) em time, datetime2 e tipos de datetimeoffset na conversão de cadeia de caracteres. O formato de cadeia de caracteres de saída com quaisquer desses estilos será transformado em um ponto (.).

Argumentos de estilo para a função CONVERT

A tabela a seguir lista exemplos de date, time, datetime2 e valores datetimeoffset para argumentos de estilo para a função CONVERT. Para obter mais informações sobre estilo, consulte a seção “Argumentos” de CAST e CONVERT (Transact-SQL)

Estilo

Associated standard

Input/Output (3)

format

date

time(n)

datetime2(n)

datetimeoffset(n)

0 ou 100 (1,2)

Padrão

mês dd aaaa hh:miAM (ou PM)

Jan 1 2001

12:20PM

Jan 1 2001 12:20PM

Jan 1 2001 12:20PM -08:00

101

EUA

mm/dd/aaaa

01/01/2001

-

01/01/2001

01/01/2001

102

ANSI

aa.mm.dd

2001.01.01

-

2001.01.01

2001.01.01

103

Britânico/francês

dd/mm/aa

01/01/2001

-

01/01/2001

01/01/2001

104

Alemão

dd.mm.aa

01.01.2001

-

01.01.2001

01.01.2001

105

Italiano

dd-mm-aa

01-01-2001

-

01-01-2001

01-01-2001

106(1)

-

dd mmm aa

01 Jan 2001

-

01 Jan 2001

01 Jan 2001

107(1)

-

Mmm dd aa

Jan 01, 2001

-

Jan 01, 2001

Jan 01, 2001

108

-

hh:mi:ss

-

12:20:20

12:20:20

12:20:20

9 ou 109 (1,2)

Padrão + milissegundos

mmm dd aaaa hh:mi:ss:mmmAM (ou PM)

Jan 1 2001

12:20:20.1234567AM

Jan 1 2001 12:20:20.1234567 PM

Jan 1 2001 12:20:20:1230000PM -08:00

110

Estados Unidos

mm-dd-aa

01-01-2001

-

01-01-2001

01-01-2001

111

JAPÃO

aa/mm/dd

2001/01/01

-

2001/01/01

2001/01/01

112

ISO

aammdd

20010101

-

20010101

20010101

13 ou 113 (1,2)

Padrão Europa + milissegundos

dd mmm aaaa hh:mi:ss:mmm (24h)

01 Jan 2001

12:20:20.1234567

01 Jan 2001 12:20:20.1234567

01 Jan 2001 12:20:20:1230000 -08:00

114

-

hh:mi:ss:mmm(24h)

-

12:20:20.1234567

12:20:20.1234567

12:20:20:1230000 -08:00

20 ou 120 (2)

ODBC canônico

aaaa-mm-dd hh:mi:ss(24h)

2001-01-01

12:20:20

2001-01-01 12:20:20

2001-01-01 12:20:20 -08:00

21 ou 121 (2)

ODBC canônico (com milissegundos)

aaaa-mm-dd hh:mi:ss.mmm(24h)

2001-01-01

12:20:20.1234567

2001-01-01 12:20:20.1234567

2001-01-01 12:20:20.1230000 -08:00

126 (4)

ISO8601

aaaa-mm.-ddThh:mi:ss.mmm (sem espaço)

2001-01-01

12:20:20.1234567

2001-01-01T 12:20:20.1234567

2001-01-01T 12:20:20.1234567

127(6, 7)

ISO8601 com fuso horário Z.

yyyy-mm-ddThh:mi:ss.mmmZ

(sem espaço)

2001-01-01

12:20:20.1234567Z

2001-01-01T 12:20:20.1234567Z

2001-01-01T20:20:20.1230000Z

130 (1,2)

Hijri (5)

dd mmm aaaa hh:mi:ss:mmmAM

01 Jan 2001

12:20:20.1230000PM

01 Jan 2001 12:20:20.1230000PM

1 Jan 2001 12:20:20:1230000PM -08:00

131 (2)

Hijri (5)

dd/mm/aa hh:mi:ss:mmmAM

01/01/2001

12:20:20.1230000PM

01/01/2001 12:20:20.1230000PM

01/01/2001 12:20:20.1230000PM -08:00

1 Esses valores de estilo retornam resultados não-determinísticos. Incluem todos os estilos (aa) (sem século) e um subconjunto de estilos (aaaaa) (com século) estilos.

2 Os valores padrão (style0 ou 100, 9 ou 109, 13 ou 113, 20 ou 120 e 21 ou 121) sempre retornam o século (aaaa).

3 Entrada quando você converte em datetime; saída quando você converte em dados de caracteres.

4 Criado para uso de XML. Para conversão de datetime ou smalldatetime em dados de caractere, o formato de saída é conforme descrito na tabela anterior.

5 Hijri é um sistema de calendário com muitas variações. O SQL Server usa o algoritmo kuwaitiano.

6 Há suporte apenas na conversão de dados de caractere em datetime ou smalldatetime. Quando os dados de caractere que representam apenas componentes de data ou de hora são convertidos em tipos de dados datetime ou smalldatetime, o componente de hora não especificado é definido como 00:00:00.000 e o componente de data não especificado é definido como 1900-01-01.

7O indicador de fuso horário opcional, Z, é usado para facilitar o mapeamento de valores datetime XML que têm informações de fuso horário em valores SQL Serverdatetime que não têm fuso horário. Z é o indicador de fuso horário UTC-0.

Limitações de data e hora do SQL Server

Na lista a seguir, a data e a hora se referem a qualquer tipo de dados de data e hora que inclui uma parte de data ou hora.

  • Não há suporte para horário de verão (DST) no lado do servidor para datetimeoffset.

  • Não há suporte ao calendário juliano para data.

  • Não há suporte de hora para a representação de ‘24’ horas para meia-noite.

  • Não há suporte de hora para o ‘segundo adicional’ acima de ‘59’.

  • Não há suporte de hora para ‘um nanossegundo’ ou maior para precisão de fração de segundo.

  • Há suporte a fuso horário para hora.

  • Não há suporte de operação INTEVAL padrão para SQL.

Compatibilidade com versões anteriores de clientes de nível inferior

Alguns clientes de nível inferior não oferecem suportea time, date, datetime2 e a tipos de dados datetimeoffset adicionados no SQL Server 2008. A tabela a seguir mostra o mapeamento de tipos entre uma instância de nível superior do SQL Server 2008 e clientes de nível inferior.

Tipos de dados do SQL Server 2008

Formato de literal de cadeia de caracteres padrão passado ao cliente de nível inferior

ODBC de nível inferior

OLEDB de nível inferior

JDBC de nível inferior

SQLCLIENT de nível inferior

time

hh:mm:ss[.nnnnnnn]

SQL_WVARCHAR ou SQL_VARCHAR

DBTYPE_WSTR ou DBTYPE_STR

Java.sql.String

Cadeia de caracteres ou SqString

date

AAAA-MM-DD

SQL_WVARCHAR ou SQL_VARCHAR

DBTYPE_WSTR ou DBTYPE_STR

Java.sql.String

Cadeia de caracteres ou SqString

datetime2

AAAA-MM-DD hh:mm:ss[.nnnnnnn]

SQL_WVARCHAR ou SQL_VARCHAR

DBTYPE_WSTR ou DBTYPE_STR

Java.sql.String

Cadeia de caracteres ou SqString

datetimeoffset

AAAA-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm

SQL_WVARCHAR ou SQL_VARCHAR

DBTYPE_WSTR ou DBTYPE_STR

Java.sql.String

Cadeia de caracteres ou SqString

Exemplos

A. Comparando tipos de dados de data e hora

O exemplo a seguir compara os resultados da conversão de um literal de cadeia de caracteres em cada tipo de dados de data e hora. A tentativa de CAST um literal de cadeia de caracteres com uma precisão de fração de segundo maior do que a permitida para smalldatetime ou datetime causará um erro.

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
        'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
        'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 
        'datetimeoffset';

Tipo de dados

Saída

time

12:35:29.1234567

date

2007-05-08

smalldatetime

2007-05-08 12:35:00

datetime

2007-05-08 12:35:29.123

datetime2(7)

2007-05-08 12:35:29.1234567

datetimeoffset(7)

2007-05-08 12:35:29.1234567 +12:15

B. Obtendo a data e a hora atuais do sistema

O exemplo a seguir mostra como usar as funções do sistema do SQL Server que retornam a data e a hora atuais. 

SELECT SYSDATETIME() AS 'SYSDATETIME'

--Results
--SYSDATETIME
--2007-10-22 14:10:41.7984554
--(1 row(s) affected)

SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'

--Results
--SYSDATETIMEOFFSET
--2007-10-22 14:11:34.2607172 -0
--(1 row(s) affected)

SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'

--Results
--SYSUTCDATETIME
--2007-10-22 21:12:11.7069470
--(1 row(s) affected)

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'

--Results
--CURRENT_TIMESTAMP
-------------------------
--2007-10-22 14:12:33.320
--(1 row(s) affected)

SELECT GETDATE() AS 'GETDATE'

--Results
--GETDATE
--2007-10-22 14:13:57.943
--(1 row(s) affected)

SELECT GETUTCDATE() AS 'GETUTCDATE'

--Results
--GETUTCDATE
--2007-10-22 21:14:35.657
--(1 row(s) affected)

C. Pesquisando todos os valores datetime2 em um dia

  • O exemplo a seguir mostra como pesquisar todos os valores de data e hora em um dia.
-- Create a table that contains with the following dates:
--     The last date-time in 2005-04-06, '2005-04-06 23:59:59.9999999'
--     The first date-time in 2005-04-07, '2005-04-07 00:00:00.0000000'
--     The last date-time in 2005-04-07, '2005-04-07 23:59:59.9999999'
--     The first date-time in 2005-04-08, '2005-04-08 00:00:00.0000000'
CREATE TABLE #Search
    (
    MyDate datetime2
    );
INSERT INTO #Search(MyDate)VALUES('2005-04-06 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 00:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-08 00:00:00.0000000');

-- The following four SELECT statements show different ways to find
-- only the two rows that contain 2005-04-07 dates.
--Use CONVERT.
SELECT MyDate 
FROM #Search 
WHERE CONVERT(date,MyDate) = '2005-04-07';

--Use >= and <=.
SELECT MyDate 
FROM #Search 
WHERE MyDate >= '2005-04-07 00:00:00.0000000' 
    AND MyDate <= '2005-04-07 23:59:59.9999999';

--Use > and <.
SELECT MyDate
FROM #Search
WHERE MyDate > '2005-04-06 23:59:59.9999999'
    AND MyDate < '2005-04-08 00:00:00.0000000';

--Use BETWEEN AND.
SELECT MyDate
FROM #Search
WHERE MyDate BETWEEN '2005-04-07 00:00:00.0000000'
    AND '2005-04-07 23:59:59.9999999';
DROP TABLE #Search
GO

D. Pesquisando um período em um dia

Os exemplos a seguir mostram como pesquisar por datas que contêm valores de hora para localizar um intervalo de tempo.

-- Create a table called Search and insert
-- five different time values for the same
-- date.

CREATE TABLE #Search
    (
    MyDate datetime2
    );

INSERT INTO #Search(MyDate)VALUES('2005-04-06 08:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 16:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 17:00:00.0000000');

-- The following SELECT statements show different ways
-- to search for dates that have time values to find a
-- time range.

--Using CONVERT with time (0) to ignore fractional seconds
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(0),MyDate) = '09:00:00';

--Returns two rows (ignores fractional seconds):
--2005-04-06 08:59:59.9999999
--2005-04-06 09:00:00.0000000

--Using CONVERT with time (7) to include fractional seconds
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(7),MyDate) = '09:00:00';

--Returns one row (matches fractional seconds):
--2005-04-06 09:00:00.0000000


--Each of the SELECT statements below use CONVERT
--to find all times in an eight-hour period.

--Use CONVERT with >= and <=.
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(7),MyDate) >=  '09:00:00.0000000'
    AND CONVERT(time(7),MyDate) <= '16:59:59.9999999'

--Use CONVERT with > and <.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) > '08:59:59.9999999'
    AND CONVERT(time(7),MyDate) < '17:00:00.0000000';

--Use CONVERT with BETWEEN AND
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) BETWEEN '09:00:00.0000000'
    AND '16:59:59.9999999';
DROP TABLE #Search
GO

E. Usando DATEPART e DATEADD para localizar o primeiro e o último dia de um datepart

O exemplo a seguir mostra como retornar o primeiro ou último dia de um datepart.

-- When several months, quarters, or years are added to the base
-- year,1900-01-01, the returned day is the first day of a month.
-- To calculate the last day of the current month, you need to
--
-- 1. Find the difference in months between today and the base
--    year (0 ='1900-01-01'). This is accomplished by
--
-- DATEDIFF(month, 0, SYSDATETIME())
-- 2. Add that number of months plus 1 to the base year (0)
--    to obtain the first day of the next month.
--    DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0)
-- 3. Subtract 1 day.
--
--Find the first day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()), 0);

--Find the last day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0) - 1;

-- Find the first day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, 0, SYSDATETIME()), 0);

-- Find the last day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, SYSDATETIME()), -1);

-- Find the first day of the current year.
SELECT DATEADD(year, DATEDIFF(year, 0, SYSDATETIME()), 0);

-- Find the last day of the current year.
SELECT DATEADD(year, DATEDIFF(year, -1, SYSDATETIME()), -1);

F. Usando argumentos datepart definidos pelo usuário para DATEADD, DATEDIFF, DATENAME e DATEPART

O exemplo a seguir cria uma função escalar definida pelo usuário que adiciona uma constante a qualquer parte de um valor datetime2.

USE tempdb
GO
CREATE FUNCTION UserDateadd
    (
    @datepart nvarchar(11)
    ,@number int 
    ,@date datetime2
    )
RETURNS datetime2
AS
BEGIN
    DECLARE @Return datetime2

    SELECT @Return = CASE @datepart
        WHEN 'year' THEN DATEADD(year,@number,@date) 
        WHEN 'yy' THEN DATEADD(year,@number,@date) 
        WHEN 'yyyy' THEN DATEADD(year,@number,@date) 
        WHEN 'quarter' THEN DATEADD(quarter,@number,@date) 
        WHEN 'qq' THEN DATEADD(quarter,@number,@date) 
        WHEN 'month' THEN DATEADD(month,@number,@date) 
        WHEN 'mm' THEN DATEADD(month,@number,@date) 
        WHEN 'm' THEN DATEADD(month,@number,@date) 
        WHEN 'dayofyear' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'dy' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'y' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'day' THEN DATEADD(day,@number,@date) 
        WHEN 'dd' THEN DATEADD(day,@number,@date) 
        WHEN 'd' THEN DATEADD(day,@number,@date) 
        WHEN 'week' THEN DATEADD(week,@number,@date) 
        WHEN 'wk' THEN DATEADD(week,@number,@date) 
        WHEN 'ww' THEN DATEADD(week,@number,@date) 
        WHEN 'weekday' THEN DATEADD(weekday,@number,@date) 
        WHEN 'wk' THEN DATEADD(weekday,@number,@date) 
        WHEN 'w' THEN DATEADD(weekday,@number,@date) 
        WHEN 'hour' THEN DATEADD(hour,@number,@date) 
        WHEN 'hh' THEN DATEADD(hour,@number,@date) 
        WHEN 'minute' THEN DATEADD(minute,@number,@date)
        WHEN 'mi' THEN DATEADD(minute,@number,@date) 
        WHEN 'n' THEN DATEADD(minute,@number,@date)  
        WHEN 'second' THEN DATEADD(second,@number,@date) 
        WHEN 'ss' THEN DATEADD(second,@number,@date)
        WHEN 's' THEN DATEADD(second,@number,@date)
        WHEN 'millisecond' THEN DATEADD(millisecond,@number,@date) 
        WHEN 'ms' THEN DATEADD(millisecond,@number,@date) 
        WHEN 'microsecond' THEN DATEADD(microsecond,@number,@date)
        WHEN 'mcs' THEN DATEADD(microsecond,@number,@date)
        WHEN 'nanosecond' THEN DATEADD(nanosecond,@number,@date)
        WHEN 'ns' THEN DATEADD(nanosecond,@number,@date)
    END
    return @Return
END
GO

SELECT GetDate()
SELECT dbo.UserDateadd('year', 1, GetDate())
GO

G. Usando DATEPART para agrupar partes de uma data

O exemplo a seguir usa o banco de dados de exemplo AdventureWorks2008R2. O DATEPART é usado para agrupar as vendas totais por dia da semana, mês, ano e ano/mês/dia da semana.

USE AdventureWorks2008R2;
GO
SELECT SUM(TotalDue) AS 'Total Sales'
    ,DATEPART(year,OrderDate)AS 'By Year'
    ,DATEPART(month,OrderDate) AS 'By Month'
    ,DATEPART(weekday,OrderDate) AS 'By Weekday'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) IN('2007','2008')
GROUP BY GROUPING SETS 
    (
     (DATEPART(year,OrderDate))
    ,(DATEPART(month,OrderDate))
    ,(DATEPART(weekday,OrderDate))
    ,(
        DATEPART(year,OrderDate)
        ,DATEPART(month,OrderDate)
        ,DATEPART(weekday,OrderDate))
    )
ORDER BY DATEPART(year,OrderDate)
    ,DATEPART(month,OrderDate)
    ,DATEPART(weekday,OrderDate);