¿Cómo puedo truncar una fecha y hora en SQL Server?

¿Cuál es la mejor manera de truncar un valor de fecha y hora (como eliminar horas, minutos y segundos) en SQL Server 2008?

Por ejemplo:

declare @SomeDate datetime = '2009-05-28 16:30:22' select trunc_date(@SomeDate) ----------------------- 2009-05-28 00:00:00.000 

Esto continúa recogiendo frecuentemente votos adicionales, incluso varios años después, por lo que necesito actualizarlo para las versiones modernas de Sql Server. Para Sql Server 2008 y posterior, es simple:

 cast(getDate() As Date) 

Tenga en cuenta que los últimos tres párrafos cerca de la parte inferior todavía se aplican, y a menudo es necesario dar un paso atrás y encontrar una manera de evitar el reparto en primer lugar.

Pero hay otras formas de lograr esto, también. Aquí están los más comunes.

La forma correcta (nueva desde Sql Server 2008):

 cast(getdate() As Date) 

La forma correcta (antigua):

 dateadd(dd, datediff(dd,0, getDate()), 0) 

Esto es más antiguo ahora, pero aún vale la pena conocerlo porque también se puede adaptar fácilmente para otros puntos de tiempo, como el primer momento del mes, minuto, hora o año.

Esta forma correcta utiliza funciones documentadas que son parte del estándar ansi y se garantiza que funcionan, pero puede ser algo más lenta. Funciona al encontrar cuántos días hay desde el día 0 hasta el día actual, y agregar eso muchos días al día 0. Funcionará sin importar cómo se almacena su fecha y no importa cuál sea su ubicación.

La manera más rápida:

 cast(floor(cast(getdate() as float)) as datetime) 

Esto funciona porque las columnas de fecha y hora se almacenan como valores binarios de 8 bytes. Póngalos a flotar, póngalos a un piso para eliminar la fracción y la porción de tiempo de los valores desaparezca cuando los vuelva a enviar a la fecha. Todo cambia un poco sin lógica complicada y es muy rápido.

Tenga en cuenta que esto depende de un detalle de implementación que Microsoft puede cambiar en cualquier momento, incluso en una actualización automática del servicio. Tampoco es muy portátil. En la práctica, es muy poco probable que esta implementación cambie en el corto plazo, pero aún así es importante ser consciente del peligro si decide usarlo. Y ahora que tenemos la opción de lanzar una fecha, rara vez es necesario.

La forma incorrecta:

 cast(convert(char(11), getdate(), 113) as datetime) 

La forma incorrecta funciona convirtiendo a una cadena, truncando la cadena y convirtiendo de nuevo a una fecha y hora. Está mal , por dos razones: 1) podría no funcionar en todos los entornos y 2) se trata de la forma más lenta posible de hacer esto … y no solo un poco; es como un orden de magnitud o dos más lento que las otras opciones.


Actualizar Esto ha estado recibiendo algunos votos últimamente, por lo que quiero agregar que, desde que publiqué esto, he visto evidencia bastante sólida de que el Servidor SQL optimizará la diferencia de rendimiento entre la forma “correcta” y la “rápida”. , lo que significa que ahora deberías favorecer al primero.

En cualquier caso, desea escribir sus consultas para evitar la necesidad de hacer esto en primer lugar . Es muy raro que deba hacer este trabajo en la base de datos.

En la mayoría de los lugares, la base de datos ya es su cuello de botella. Por lo general, el servidor es el más caro para agregar hardware a las mejoras de rendimiento y el más difícil para obtener esas adiciones correctas (tiene que equilibrar los discos con la memoria, por ejemplo). También es el más difícil de escalar hacia afuera, tanto desde el punto de vista técnico como comercial; es mucho más fácil técnicamente agregar un servidor web o de aplicaciones que un servidor de base de datos e incluso si eso fuera falso, no pagaría $ 20,000 + por licencia de servidor para IIS o apache.

El punto que trato de hacer es que siempre que sea posible, debe hacer este trabajo en el nivel de la aplicación. La única vez que debe encontrarse truncando una fecha y hora en el servidor Sql es cuando necesita agrupar por día, e incluso entonces probablemente debería tener una columna adicional configurada como columna calculada, mantenida en el momento de insertar / actualizar o mantenida en la lógica de la aplicación. Obtenga este trabajo desglosador y pesado de su base de datos.

Solo para SQL Server 2008

 CAST(@SomeDateTime AS Date) 

Luego devuélvelo a datetime si quieres

 CAST(CAST(@SomeDateTime AS Date) As datetime) 

Solo por el bien de una respuesta más completa, aquí hay una forma efectiva de truncar a cualquiera de las partes de fecha e incluyendo minutos (reemplace GETDATE() con la fecha para truncar).

Esto es diferente de la respuesta aceptada, ya que puede usar no solo dd (días), sino cualquiera de las partes de la fecha (ver aquí ):

 dateadd(minute, datediff(minute, 0, GETDATE()), 0) 

Tenga en cuenta que en la expresión anterior, el 0 es una fecha constante al comienzo de un año (1900-01-01). Si necesita truncar en partes más pequeñas, como segundos o milisegundos, debe tomar una fecha constante que esté más cerca de la fecha que se truncará para evitar un desbordamiento.

El fragmento que encontré en la web cuando tuve que hacer esto fue:

  dateadd(dd,0, datediff(dd,0, YOURDATE)) eg dateadd(dd,0, datediff(dd,0, getDate())) 

En SQl 2005 su función trunc_date podría escribirse así.

(1)

 CREATE FUNCTION trunc_date(@date DATETIME) RETURNS DATETIME AS BEGIN CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME) END 

El primer método es mucho más limpio. Utiliza solo 3 llamadas a métodos, incluido el CAST final () y no realiza ninguna concatenación de cadenas, que es un valor añadido automático. Además, no hay moldes de tipo enorme aquí. Si puede imaginarse que se pueden representar los sellos de fecha / hora, entonces la conversión de fechas a números y de vuelta a fechas es un proceso bastante fácil.

(2)

 CREATE FUNCTION trunc_date(@date DATETIME) RETURNS DATETIME AS BEGIN SELECT CONVERT(varchar, @date,112) END 

Si le preocupa la implementación de Microsoft de las fechas (2) o (3), podría estar bien.

(3)

 CREATE FUNCTION trunc_date(@date DATETIME) RETURNS DATETIME AS BEGIN SELECT CAST((STR( YEAR( @date ) ) + '/' +STR( MONTH( @date ) ) + '/' +STR( DAY(@date ) ) ) AS DATETIME END 

En tercer lugar, el método más detallado. Esto requiere dividir la fecha en sus partes de año, mes y día, juntarlas en formato “aaaa / mm / dd”, y luego volverlas a una fecha. Este método involucra 7 llamadas a métodos incluyendo el CAST final (), sin mencionar la concatenación de cadenas.

 CONVERT(DATE, ) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP) 

select cast (floor (cast (getdate () como float)) como datetime) Referencia esto: http://microsoftmiles.blogspot.com/2006/11/remove-time-from-datetime-in-sql-server.html

Para aquellos de ustedes que vinieron aquí buscando una manera de truncar un campo DATETIME a algo menos de un día completo, por ejemplo, cada minuto, pueden usar esto:

 SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) + (FLOOR((CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 1440.0) + (3.0/86400000.0)) / 1440.0 AS DATETIME) 

entonces si hoy fuera 2010-11-26 14:54:43.123 entonces esto volvería 2010-11-26 14:54:00.000 .

Para cambiar el intervalo al que se dirige, reemplace 1440.0 por el número de intervalos en un día, por ejemplo:

 24hrs = 24.0 (for every hour) 24hrs / 0.5hrs = 48.0 (for every half hour) 24hrs / (1/60) = 1440.0 (for every minute) 

(Siempre ponga un .0 en el extremo para lanzar implícitamente a un flotante).


Para aquellos de ustedes que se preguntan para qué (3.0/86400000) el (3.0/86400000) en mi cálculo, SQL Server 2005 no parece emitir de FLOAT a DATETIME precisión, por lo que esto agrega 3 milisegundos antes de pavimentarlo.

Esta consulta debería darle un resultado equivalente a trunc(sysdate) en Oracle.

 SELECT * FROM your_table WHERE CONVERT(varchar(12), your_column_name, 101) = CONVERT(varchar(12), GETDATE(), 101) 

¡Espero que esto ayude!

También puede extraer la fecha using Substring desde la variable de fecha y hora y volver a la fecha ignorará la parte de tiempo.

 declare @SomeDate datetime = '2009-05-28 16:30:22' SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime) 

Además, puede acceder a partes de la variable de fecha y hora y combinarlas con una fecha truncada de construcción, algo como esto:

 SELECT cast(DATENAME(year, @Somedate) + '-' + Convert(varchar(2),DATEPART(month, @Somedate)) + '-' + DATENAME(day, @Somedate) as datetime) 

Oráculo:

 TRUNC(SYSDATE, 'MONTH') 

Servidor SQL:

 DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField) 

Podría usarse de manera similar para truncar minutos u horas de una fecha.

usted podría hacer esto (SQL 2008):

declarar @SomeDate date = getdate ()

 select @SomeDate 

2009-05-28

TRUNC (aDate, ‘DD’) truncará el min, sec y hrs

SRC: http://www.techonthenet.com/oracle/functions/trunc_date.php