DATETRUNC (Transact-SQL)
Se aplica a:
SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstancePunto de conexión de análisis SQL en Microsoft FabricAlmacenamiento en Microsoft Fabric
La función DATETRUNC devuelve una fecha de entrada truncada a un datepart especificado.
Sintaxis
DATETRUNC ( datepart, date )
Argumentos
datepart
Especifica la precisión para el truncamiento. En esta tabla se enumeran todos los valores datepart válidos para DATETRUNC, dado que también se trata de una parte válida del tipo de fecha de entrada.
| datepart | Abreviaturas | Notas de truncamiento |
|---|---|---|
| year | yy, yyyy | |
| quarter | qq, q | |
| month | mm, m | |
| dayofyear | dy, y | dayofyear se trunca de la misma manera que day |
| day | dd, d | day se trunca de la misma manera que dayofyear |
| week | wk, ww | Trunque al primer día de la semana. En T-SQL, el primer día de la semana se define mediante la configuración T-SQL @@DATEFIRST. Para un entorno de Inglés (EE.UU.), el valor predeterminado de @@DATEFIRST es 7 (domingo). |
| iso_week | isowk, isoww | Trunque al primer día de una semana ISO. El primer día de la semana del sistema de calendario ISO8601 es el lunes. |
| hour | hh | |
| minute | mi, n | |
| second | ss, s | |
| millisecond | ms | |
| microsecond | mcs |
Nota
Los elementos datepart T-SQL weekday, timezoneoffset y nanosecond no se admiten para DATETRUNC.
date
Acepta cualquier expresión, columna o variable definida por el usuario que pueda resolverse en cualquier tipo de fecha u hora T-SQL válido. Los tipos válidos son los siguientes:
- smalldatetime
- datetime
- date
- time
- datetime2
- datetimeoffset
No confunda el parámetro date con el tipo de datos date.
DATETRUNC también aceptará un literal de cadena (de cualquier tipo de cadena) que se pueda resolver en un valor datetime2(7).
Tipo de valor devuelto
El tipo de datos devuelto para DATETRUNC es dinámico. DATETRUNC devuelve una fecha truncada del mismo tipo de datos (y, si procede, la misma escala de tiempo fraccionaria) que la fecha de entrada. Por ejemplo, si DATETRUNC recibiera una fecha de entrada datetimeoffset(3), devolvería un valor datetimeoffset(3). Si recibiera un literal de cadena capaz de resolverse en un valor datetime2(7), DATETRUNC devolvería un valor datetime2(7).
Precisión de la escala de tiempo fraccionaria
Los milisegundos tienen una escala de tiempo fraccionaria de 3 (.123), los microsegundos tienen una escala de tiempo fraccionaria de 6 (.123456) y los nanosegundos tienen una escala de tiempo fraccionaria de 9 (.123456789). Los tipos de datos time, datetime2 y datetimeoffset permiten una escala de tiempo fraccionaria máxima de 7 (.1234567). Por lo tanto, para truncar al datepartmillisecond, la escala de tiempo fraccionaria debe ser de al menos 3. Del mismo modo, para truncar al datepartmicrosecond, la escala de tiempo fraccionaria debe ser de al menos 6. DATETRUNC no admite el datepartnanosecond, ya que ningún tipo de fecha T-SQL admite una escala de tiempo fraccionaria de 9.
Ejemplos
A. Usar diferentes opciones de datepart
En los ejemplos siguientes se muestra el uso de los distintas opciones 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 es el 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. Configuración @@DATEFIRST
En los ejemplos siguientes se muestra el uso de la configuración @@DATEFIRST con el datepartweek:
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 es el 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. Literales de fecha
En los ejemplos siguientes se muestra el uso de literales del 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 es el conjunto de resultados (todos los resultados son de 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. Variables y el parámetro date
En los ejemplos siguientes se muestra el uso del parámetro date:
DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);
Este es el resultado:
1998-12-11 00:00:00.0000000
E. Columnas y el parámetro date
La columna TransactionDate de la tabla Sales.CustomerTransactions actúa como argumento column de ejemplo para el 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. Expresiones y el parámetro date
El parámetro date acepta cualquier expresión que pueda resolverse en un tipo de fecha T-SQL o cualquier literal de cadena que pueda resolverse en un elemento datetime2(7). La columna TransactionDate de la tabla Sales.CustomerTransactions actúa como argumento artificial para ejemplificar el uso de un elemento expression para el 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. Truncamiento de date a un datepart que representa su máxima precisión
Si el datepart tiene la misma precisión máxima de unidad que el tipo de fecha de entrada, el truncamiento de la fecha de entrada a este datepart no tendría ningún efecto.
Ejemplo 1
DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);
Este es el conjunto de resultados, donde se muestra que la datetime de entrada y el parámetro date truncada son el mismo:
Input 2015-04-29 05:06:07.123
Truncated 2015-04-29 05:06:07.123
Ejemplo 2
DECLARE @d date = '2050-04-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);
Este es el conjunto de resultados, donde se muestra que la datetime de entrada y el parámetro date truncada son el mismo:
Input 2050-04-04
Truncated 2050-04-04
Example 3: Precisión smalldatetime
smalldatetime solo es preciso hasta el minuto más cercano, aunque tenga un campo para segundos. Por lo tanto, truncarlo al minuto o al segundo más cercano no tendría ningún efecto.
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);
En el conjunto de resultados se muestra que el valor smalldatetime de entrada es el mismo que la de los dos 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
Ejemplo 4: Precisión datetime
datetime solo es preciso hasta 3,33 milisegundos. Por lo tanto, el truncamiento de un elemento datetime a un milisegundo puede producir resultados diferentes a los esperados por el usuario. Sin embargo, este valor truncado es igual que el valor de datetime almacenado internamente.
DECLARE @d datetime = '2020-02-02 02:02:02.002';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);
Este es el conjunto de resultados, donde se muestra que el valor date truncado es igual que el valor date almacenado. Esto puede ser diferente a lo que espera en función de la instrucción DECLARE.
Input 2020-02-02 02:02:02.003
Truncated 2020-02-02 02:02:02.003
Observaciones
Se produce un error DATE TOO SMALL si el truncamiento de date intenta retroceder a una fecha anterior a la fecha mínima admitida por ese tipo de datos. Este problema solo se produce cuando se usa el datepartweek. No se puede producir cuando se usa el elemento datepartiso_week, ya que todos los tipos de fecha T-SQL casualmente usan el lunes para sus fechas mínimas. Este es un ejemplo con el mensaje de error del resultado correspondiente:
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.
Se produce un error DATEPART si la función DATETRUNC o el tipo de datos de fecha de entrada no admite el elemento datepart usado. Esto puede suceder si:
Se usa un elemento datepart no admitido por
DATETRUNC(es decir,weekday,tzoffsetonanosecond)Un elemento datepart relacionado con time se usa con el tipo de datos date o un datepart relacionado con date se usa con el tipo de datos time. Este es un ejemplo con el mensaje de error del resultado correspondiente:
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.El elemento datepart requiere una precisión de escala de tiempo fraccionaria mayor que la que admite el tipo de datos (consulte la sección sobre la precisión de escala de tiempo fraccionaria). Este es un ejemplo con el mensaje de error del resultado correspondiente:
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.
Vea también
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de