DATETRUNC (Transact-SQL)

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Ponto de extremidade de análise de SQL no Microsoft Fabric Warehouse no Microsoft Fabric

A função DATETRUNC retorna uma data de entrada truncada para um datepart especificado.

Sintaxe

DATETRUNC ( datepart , date )

Argumentos

datepart

Especifica a precisão do truncamento. Essa tabela lista todos os valores de datepart válidos para DATETRUNC, desde que ela também seja uma parte válida do tipo de data de entrada.

date

Aceita qualquer expressão, coluna ou variável definida pelo usuário que possa ser resolvida para qualquer tipo de data ou hora T-SQL válido. Os tipos válidos são:

• smalldatetime
• datetime
• date
• time
• datetime2
• datetimeoffset

Não confunda o parâmetro date com o tipo de dados date.

DATETRUNCtambém aceita um literal de cadeia de caracteres (de qualquer tipo de cadeia de caracteres) que pode ser resolvido para um datetime2(7).

Tipos de retorno

O tipo de dados retornado para DATETRUNC é dinâmico. DATETRUNC retorna uma data truncada do mesmo tipo de dados (e, se aplicável, a mesma escala de tempo fracionária) que a data de entrada. Por exemplo, se DATETRUNC tiver recebido uma data de entrada datetimeoffset(3), retornará um datetimeoffset(3). Se recebesse um literal de cadeia de caracteres que poderia ser resolvido para um datetime2(7), DATETRUNC retornaria um datetime2(7).

Precisão de escala de tempo fracionária

Os milissegundos têm uma escala de tempo fracionária de 3 (.123), os microssegundos têm uma escala de tempo fracionária de 6 (.123456) e os nanossegundos têm uma escala de tempo fracionária de 9 (.123456789). Os tipos de dados time, datetime2 e datetimeoffset permitem uma escala máxima de tempo fracionária de 7 (.1234567). Portanto, para truncar para a millisecond parte da data, a escala de tempo fracionária deve ser de pelo menos 3. Da mesma forma, para truncar para a microsecond dataparte, a escala de tempo fracionária deve ser de pelo menos 6. DATETRUNC não dá suporte ao nanosecond datepart , pois nenhum tipo de data T-SQL dá suporte a uma escala de tempo fracionária de 9.

Exemplos

a. Usar diferentes opções de datepart

Os seguintes exemplos ilustram o uso das várias opções de datepart:

DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);

Veja a seguir o conjunto de resultados.

Year        2021-01-01 00:00:00.0000000
Quarter     2021-10-01 00:00:00.0000000
Month       2021-12-01 00:00:00.0000000
Week        2021-12-05 00:00:00.0000000
Iso_week    2021-12-06 00:00:00.0000000
DayOfYear   2021-12-08 00:00:00.0000000
Day         2021-12-08 00:00:00.0000000
Hour        2021-12-08 11:00:00.0000000
Minute      2021-12-08 11:30:00.0000000
Second      2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560

B. Configuração @@DATEFIRST

Os exemplos a seguir ilustram o @@DATEFIRST uso da configuração com a week datepart:

DECLARE @d datetime2 = '2021-11-11 11:11:11.1234567';

SELECT 'Week-7', DATETRUNC(week, @d); -- Uses the default DATEFIRST setting value of 7 (U.S. English)

SET DATEFIRST 6;
SELECT 'Week-6', DATETRUNC(week, @d);

SET DATEFIRST 3;
SELECT 'Week-3', DATETRUNC(week, @d);

Veja a seguir o conjunto de resultados.

Week-7  2021-11-07 00:00:00.0000000
Week-6  2021-11-06 00:00:00.0000000
Week-3  2021-11-10 00:00:00.0000000

C. Literais de data

Os seguintes exemplos ilustram o uso de literais de parâmetro date:

SELECT DATETRUNC(month, '1998-03-04');

SELECT DATETRUNC(millisecond, '1998-03-04 10:10:05.1234567');

DECLARE @d1 char(200) = '1998-03-04';
SELECT DATETRUNC(millisecond, @d1);

DECLARE @d2 nvarchar(max) = '1998-03-04 10:10:05';
SELECT DATETRUNC(minute, @d2);

Veja a seguir o conjunto de resultados. Todos os resultados são do tipo datetime2(7).

1998-03-01 00:00:00.0000000
1998-03-04 10:10:05.1230000
1998-03-04 00:00:00.0000000
1998-03-04 10:10:00.0000000

D. Variáveis e o parâmetro date

O exemplo a seguir ilustra o uso do parâmetro date:

DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);

Veja a seguir o conjunto de resultados.

1998-12-11 00:00:00.0000000

E. Colunas e o parâmetro date

A coluna TransactionDate da tabela Sales.CustomerTransactions funciona como um argumento column de exemplo para o parâmetro date:

USE WideWorldImporters;
GO

SELECT CustomerTransactionID,
    DATETRUNC(month, TransactionDate) AS MonthTransactionOccurred,
    InvoiceID,
    CustomerID,
    TransactionAmount,
    SUM(TransactionAmount) OVER (
        PARTITION BY CustomerID ORDER BY TransactionDate,
            CustomerTransactionID ROWS UNBOUNDED PRECEDING
        ) AS RunningTotal,
    TransactionDate AS ActualTransactionDate
FROM [WideWorldImporters].[Sales].[CustomerTransactions]
WHERE InvoiceID IS NOT NULL
    AND DATETRUNC(month, TransactionDate) >= '2015-12-01';

F. Expressões e o parâmetro date

O parâmetro date aceita qualquer expressão que possa ser resolvida para um tipo de data T-SQL ou qualquer literal de cadeia de caracteres que possa ser resolvido para um datetime2(7). A coluna TransactionDate da tabela Sales.CustomerTransactions funciona como uma argumento artificial para exemplificar o uso de uma expressão para o parâmetro date:

SELECT DATETRUNC(m, SYSDATETIME());

SELECT DATETRUNC(yyyy, CONVERT(date, '2021-12-1'));

USE WideWorldImporters;
GO
SELECT DATETRUNC(month, DATEADD(month, 4, TransactionDate))
FROM Sales.CustomerTransactions;
GO

G. Truncar uma date para um datepart que representa sua precisão máxima

Se o datepart tiver a mesma precisão máxima de unidade que o tipo de data de entrada, truncar a data de entrada para esse datepart não terá efeito.

Exemplo 1

DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

Veja a seguir o conjunto de resultados. O parâmetro datetime de entrada e o parâmetro de data truncado são os mesmos.

Input     2015-04-29 05:06:07.123
Truncated 2015-04-29 05:06:07.123

Exemplo 2

DECLARE @d date = '2050-04-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);

Veja a seguir o conjunto de resultados. O parâmetro datetime de entrada e o parâmetro de data truncado são os mesmos.

Input     2050-04-04
Truncated 2050-04-04

Exemplo 3: precisão smalldatetime

smalldatetime só é precisa até o minuto mais próximo, embora tenha um campo por segundos. Portanto, truncá-lo para o minuto mais próximo ou o segundo mais próximo não teria efeito.

DECLARE @d smalldatetime = '2009-09-11 12:42:12'
SELECT 'Input', @d;
SELECT 'Truncated to minute', DATETRUNC(minute, @d)
SELECT 'Truncated to second', DATETRUNC(second, @d);

Veja a seguir o conjunto de resultados. O valor smalldatetime de entrada é o mesmo que ambos os valores truncados:

Input                2009-09-11 12:42:00
Truncated to minute  2009-09-11 12:42:00
Truncated to second  2009-09-11 12:42:00

Exemplo 4: precisão de datetime

datetime só é preciso até 3,33 milissegundos. Portanto, truncar um datetime para um milissegundo pode produzir resultados diferentes do que o usuário espera. No entanto, esse valor truncado é o mesmo que o valor de datetime armazenado internamente.

DECLARE @d datetime = '2020-02-02 02:02:02.002';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

Veja a seguir o conjunto de resultados. A data truncada é a mesma que a data armazenada. Isso pode ser diferente do que você espera com base na DECLARE declaração.

Input     2020-02-02 02:02:02.003
Truncated 2020-02-02 02:02:02.003

Comentários

Um erro será gerado se o truncamento de data tentar retroceder para uma data anterior à data mínima suportada por esse tipo de dados. Esse erro ocorre apenas ao usar o week datepart. Isso não pode ocorrer ao usar o iso_week datepart, pois todos os tipos de data T-SQL coincidentemente usam uma segunda-feira para suas datas mínimas. Este é um exemplo com a mensagem de erro de resultado correspondente:

DECLARE @d date= '0001-01-01 00:00:00';
SELECT DATETRUNC(week, @d);

Msg 9837, Level 16, State 3, Line 84
An invalid date value was encountered: The date value is less than the minimum date value allowed for the data type.

Um DATEPART erro será gerado se a DATETRUNC função ou o tipo de dados de data de entrada não der suporte à parte de data usada. Esse erro pode acontecer quando:

1. Um datepart sem suporte de DATETRUNC é usado (ou seja, weekday, tzoffset ou nanosecond)

2. Um datepart relacionado a time é usado com o tipo de dados date ou um datepart relacionado a date é usado com o tipo de dados time. Este é um exemplo com a mensagem de erro de resultado correspondente:

   DECLARE @d time = '12:12:12.1234567';
   SELECT DATETRUNC(year, @d);
   

   Msg 9810, Level 16, State 10, Line 78
   The datepart year is not supported by date function datetrunc for data type time.
   

3. A datepart requer uma precisão de escala de tempo fracionária maior do que a suportada pelo tipo de dados. Para obter mais informações, consulte Precisão da escala de tempo fracionária. Este é um exemplo com a mensagem de erro de resultado correspondente:

   DECLARE @d datetime2(3) = '2021-12-12 12:12:12.12345';
   SELECT DATETRUNC(microsecond, @d);
   

   Msg 9810, Level 16, State 11, Line 81
   The datepart microsecond is not supported by date function datetrunc for data type datetime2.
   

Conteúdo relacionado

• @@DATEFIRST (Transact-SQL)
• DATEPART (Transact-SQL)
• Tipos de dados e funções de data e hora (Transact-SQL)