DATETRUNC (Transact-SQL)

Artigo
05/23/2023

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álises 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.

datepart	Abreviações	Observações sobre truncamento
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y	dayofyear é truncado da mesma maneira que day
day	dd, d	day é truncado da mesma maneira que dayofyear
week	wk, ww	Faça o truncamento para o primeiro dia da semana. Em T-SQL, o primeiro dia da semana é definido pela configuração T-SQL `@@DATEFIRST`. Para um ambiente em inglês dos Estados Unidos, `@@DATEFIRST` assume o valor 7 (domingo) por padrão.
iso_week	isowk, isoww	Faça o truncamento para o primeiro dia de uma semana ISO. O primeiro dia da semana no sistema de calendário ISO8601 é segunda-feira.
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs

Observação

Não há suporte para os dateparts T-SQL weekday, timezoneoffset e nanosecond em DATETRUNC.

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.

DATETRUNC também aceitará um literal de cadeia de caracteres (de qualquer tipo de cadeia de caracteres) que possa ser resolvido para um datetime2(7).

Tipo 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 o milliseconddatepart, a escala de tempo fracionária deve ser de pelo menos 3. Portanto, para truncar para o microseconddatepart, a escala de tempo fracionária deve ser de pelo menos 6. DATETRUNC não dá suporte ao nanoseconddatepart, pois nenhum tipo de dados 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);

Este é 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 seguintes exemplos ilustram o uso da configuração @@DATEFIRST com o weekdatepart:

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);

Este é 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);

Este é 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);

Eis o resultado:

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;

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);

Este é o conjunto de resultados, que ilustra que a entrada datetime e o parâmetro date truncado são iguais:

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);

Este é o conjunto de resultados, que ilustra que a entrada datetime e o parâmetro date truncado são iguais:

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);

O conjunto de resultados ilustra que o valor da entrada smalldatetime é igual aos 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 gerar resultados diferentes do esperado pelo usuário. 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);

Este é o conjunto de resultados, que ilustra que a date truncada é igual à date armazenada. Isso pode ser diferente do esperado com base na instrução DECLARE.

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

Comentários

Um erro DATE TOO SMALL será gerado se o truncamento de date tentar voltar para uma data anterior à data mínima compatível com esse tipo de data. Isso só ocorre ao usar o weekdatepart. Não pode ocorrer ao usar o iso_weekdatepart, pois todos os tipos de data T-SQL coincidentemente usam uma segunda-feira para as datas mínimas deles. 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 erro DATEPART será gerado se o datepart usado não for compatível com a função DATETRUNC nem com o tipo de dados de data de entrada. Isso pode ocorrer quando:

Um datepart sem suporte de DATETRUNC é usado (ou seja, weekday, tzoffset ou nanosecond)
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.
```
O datepart requer uma precisão de escala de tempo fracionária maior do que o que é compatível com o tipo de dados (confira Precisão de 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.
```

DATETRUNC (Transact-SQL)

Sintaxe

Argumentos

datepart

date

Tipo de retorno

Precisão de escala de tempo fracionária

Exemplos

a. Usar diferentes opções de datepart

B. Configuração @@DATEFIRST

C. Literais de data

D. Variáveis e o parâmetro date

E. Colunas e o parâmetro date

F. Expressões e o parâmetro date

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

Exemplo 1

Exemplo 2

Exemplo 3: precisão smalldatetime

Exemplo 4: precisão de datetime

Comentários

Confira também

Comentários

Comentários

Recursos adicionais