truncar trunc month fechas fecha convertir convert sql-server sql-server-2008 datetime truncate

month - ¿Cómo puedo truncar un datetime en SQL Server?



trunc date sql server 2012 (13)

¿Cuál es la mejor manera de truncar un valor de fecha y hora (para eliminar horas y minutos) 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

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

dateadd(dd,0, datediff(dd,0, YOURDATE)) e.g. 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 de método, incluido el último CAST () y no realiza ninguna concatenación de cadenas, lo que es una ventaja automática. Además, aquí no hay grandes tipos de moldes. Si puede imaginar que los sellos de fecha / hora se pueden representar, entonces convertir de fechas a números y volver 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 fechas de datos de Microsoft (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, juntándolas en formato "aaaa / mm / dd", y luego volver a poner esa fecha en una fecha. Este método involucra 7 llamadas de método incluyendo el último CAST (), sin mencionar la concatenación de cadenas.

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!

Esto sigue acumulando 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 versiones posteriores, es simple:

cast(getDate() As Date)

Tenga en cuenta que los últimos tres párrafos cerca de la parte inferior siguen siendo válidos, y que a menudo necesita retroceder un paso y encontrar una manera de evitar el lanzamiento en primer lugar.

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

La forma correcta (nueva desde Sql Server 2008):

cast(getdate() As Date)

La forma correcta (antiguo):

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

Esto es más antiguo ahora, pero aún vale la pena saberlo porque también puede adaptarse fácilmente a 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 funcionen, pero puede ser algo más lento. Funciona encontrando cuántos días hay desde el día 0 hasta el día actual, y agregando muchos días anteriores al día 0. Funcionará sin importar cómo se almacene su fecha y hora y sin importar cuál sea su ubicación.

La forma 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. Emítalos para flotar, colóquelos en el piso para eliminar la fracción, y la parte de tiempo de los valores desaparece cuando los devuelve a datetime. Todo está cambiando un poco sin lógica complicada y es muy rápido.

Tenga en cuenta que esto se basa en 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 pronto, pero aún así es importante estar al tanto del peligro si elige usarlo. Y ahora que tenemos la opción de emitir como fecha, rara vez es necesario.

La forma incorrecta:

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

La forma incorrecta funciona convirtiéndose en una cadena, truncando la cadena y volviendo a convertir en una fecha y hora. Está mal , por dos razones: 1) puede que no funcione en todos los entornos locales 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 obteniendo algunos votos últimamente, por lo que quiero agregarle que, desde que publiqué esto, he visto algunas pruebas bastante sólidas de que el servidor Sql optimizará la diferencia de rendimiento entre la forma "correcta" y la forma "rápida". , lo que significa que ahora debe favorecer a la primera.

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

En la mayoría de los lugares, la base de datos ya es su cuello de botella. En general, es el servidor en el que es más costoso agregar hardware para las mejoras de rendimiento y el más difícil para hacer esas adiciones correctamente (por ejemplo, debe equilibrar los discos con la memoria). También es el más difícil de escalar hacia el exterior, tanto desde el punto de vista técnico como desde el punto de vista empresarial; técnicamente, es mucho más fácil agregar un servidor web o de aplicaciones que un servidor de base de datos, e incluso si eso fuera falso, no paga $ 20,000 por licencia de servidor para IIS o Apache.

El punto que trato de señalar es que siempre que sea posible, debe hacer este trabajo en el nivel de la aplicación. La única vez que debería estar 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 una columna computada, mantenida en el momento de insertar / actualizar, o mantenida En la lógica de la aplicación. Obtenga este trabajo de ruptura de índices y CPU de su base de datos.

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 a partir de una fecha.

Para aquellos de ustedes que vinieron aquí buscando una forma de truncar un campo DATETIME a algo menos de un día entero, 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)

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

Para cambiar el intervalo al que se refiere, reemplace 1440.0 con 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 flotador).

Para aquellos de ustedes que se preguntan a qué se debe el (3.0/86400000) en mi cálculo, SQL Server 2005 no parece realizar la FLOAT de FLOAT a DATETIME precisa, por lo que esto agrega 3 milisegundos antes del piso.

Solo para SQL Server 2008

CAST(@SomeDateTime AS Date)

Luego, vuelve a datetime si quieres

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

Solo por una respuesta más completa, aquí hay una manera práctica de truncar a cualquiera de las partes de la fecha e incluir los minutos (reemplace GETDATE() con la fecha a truncar).

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

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 truncarse en partes más pequeñas, como segundos o milisegundos, debe tomar una fecha constante que esté más cerca de la fecha a truncar para evitar un desbordamiento.

También puede extraer la fecha using Substring de la variable datetime y volver a la fecha y hora ignorará la parte del 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 datetime y combinarlas en 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)

puedes hacer esto (SQL 2008):

declare @SomeDate date = getdate ()

select @SomeDate

2009-05-28

CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)