mes - obtener hora sql server

¿Cuál es una buena manera de verificar si dos fechas y horas están en el mismo día calendario en TSQL?

Question

Este es el problema que estoy teniendo: Tengo una gran consulta que necesita comparar fechas y horas en la cláusula where para ver si dos fechas están en el mismo día. Mi solución actual, que apesta, es enviar las fechas en UDF para convertirlas a la medianoche del mismo día, y luego verificar esas fechas por igualdad. Cuando se trata del plan de consulta, se trata de un desastre, como casi todas las UDF en cláusulas join o where. Este es uno de los únicos lugares en mi aplicación que no he podido eliminar de raíz las funciones y darle al optimizador de consultas algo que realmente puede usar para ubicar el mejor índice.

En este caso, fusionar el código de función nuevamente en la consulta parece poco práctico.

Creo que me falta algo simple aquí.

Aquí está la función de referencia.

if not exists (select * from dbo.sysobjects 
              where id = object_id(N''dbo.f_MakeDate'') and               
              type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT''))
  exec(''create function dbo.f_MakeDate() returns int as 
         begin declare @retval int return @retval end'')
go

alter function dbo.f_MakeDate
(
    @Day datetime, 
    @Hour int, 
    @Minute int
)
returns datetime
as

/*

Creates a datetime using the year-month-day portion of @Day, and the 
@Hour and @Minute provided

*/

begin

declare @retval datetime
set @retval = cast(
    cast(datepart(m, @Day) as varchar(2)) + 
    ''/'' + 
    cast(datepart(d, @Day) as varchar(2)) + 
    ''/'' + 
    cast(datepart(yyyy, @Day) as varchar(4)) + 
    '' '' + 
    cast(@Hour as varchar(2)) + 
    '':'' + 
    cast(@Minute as varchar(2)) as datetime)
return @retval
end

go

Para complicar las cosas, me estoy uniendo a las tablas de zona horaria para verificar la fecha con la hora local, que podría ser diferente para cada fila:

where 
dbo.f_MakeDate(dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, t.TheDateINeedToCheck), 0, 0) = @activityDateMidnight

[Editar]

Estoy incorporando la sugerencia de @ Todd:

where datediff(day, dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, t.TheDateINeedToCheck), @ActivityDate) = 0

Mi idea errónea sobre cómo funciona la fecha de caducidad (el mismo día del año en años consecutivos arroja 366, no 0 como esperaba) me hizo perder mucho esfuerzo.

Pero el plan de consulta no cambió. Creo que necesito volver al tablero de dibujo con todo el asunto.

Answer 1

Asegúrese de leer Solo en una base de datos ¿Puede obtener una mejora del 1000% + mediante el cambio de unas pocas líneas de código para asegurarse de que el optimizador puede utilizar el índice de manera efectiva cuando se juega con las fechas?

Answer 2

En cuanto a las zonas horarias, una razón más para almacenar todas las fechas en una sola zona horaria (preferiblemente UTC). De todos modos, creo que las respuestas que usan dateiff, datepart y las diferentes funciones de fecha integradas son su mejor opción.

Answer 3

Eric Z Beard:

Guardo todas las fechas en GMT. Aquí está el caso de uso: algo sucedió a las 11:00 PM EST del 1er, que es el 2º GMT. Quiero ver actividad para el 1er, y estoy en EST así que querré ver la actividad de las 11PM. Si solo comparase las fechas de GMT crudas, extrañaría las cosas. Cada fila en el informe puede representar una actividad de un huso horario diferente.

Correcto, pero cuando dice que le interesa la actividad para el 1 de enero de 2008 EST:

SELECT @activityDateMidnight = ''1/1/2008'', @activityDateTZ = ''EST''

solo necesitas convertir eso a GMT (estoy ignorando la complicación de consultar el día antes de que EST vaya a EDT, o viceversa):

Table: TimeZone
Fields: TimeZone, Offset
Values: EST, -4

--Multiply by -1, since we''re converting EST to GMT.
--Offsets are to go from GMT to EST.
SELECT @activityGmtBegin = DATEADD(hh, Offset * -1, @activityDateMidnight)
FROM TimeZone
WHERE TimeZone = @activityDateTZ

que debería darte ''1/1/2008 4:00 AM''. Luego, solo puede buscar en GMT:

SELECT * FROM EventTable
WHERE 
   EventTime >= @activityGmtBegin --1/1/2008 4:00 AM
   AND EventTime < (@activityGmtBegin + 1) --1/2/2008 4:00 AM

El evento en cuestión se almacena con un GMT EventTime de 1/2/2008 3:00 AM. Ni siquiera necesita el TimeZone en EventTable (para este propósito, al menos).

Como EventTime no tiene una función, esta es una exploración de índice directa, que debería ser bastante eficiente. Haga de EventTime su índice agrupado, y volará. ;)

Personalmente, haría que la aplicación convirtiera el tiempo de búsqueda en GMT antes de ejecutar la consulta.

Answer 4

Esto es mucho más conciso:

where 
  datediff(day, date1, date2) = 0

Answer 5

Tienes muchas opciones en términos de opciones aquí. Si está utilizando Sybase o SQL Server 2008, puede crear variables de tipo fecha y asignarles sus valores de fecha y hora. El motor de la base de datos se deshace del tiempo para usted. Aquí hay una prueba rápida y sucia para ilustrar (Code is in Sybase dialect):

declare @date1 date
declare @date2 date
set @date1=''2008-1-1 10:00''
set @date2=''2008-1-1 22:00''
if @date1=@date2
    print ''Equal''
else
    print ''Not equal''

Para SQL 2005 y anteriores, lo que puede hacer es convertir la fecha a un varchar en un formato que no tenga el componente de tiempo. Por ejemplo, lo siguiente devuelve 2008.08.22

select convert(varchar,''2008-08-22 18:11:14.133'',102)

La parte 102 especifica el formato (los libros en línea pueden mostrarle todos los formatos disponibles)

Entonces, lo que puedes hacer es escribir una función que tome una fecha y extraiga el elemento de fecha y descarte la hora. Al igual que:

create function MakeDate (@InputDate datetime) returns datetime as
begin
    return cast(convert(varchar,@InputDate,102) as datetime);
end

A continuación, puede utilizar la función para acompañantes

Select * from Orders where dbo.MakeDate(OrderDate) = dbo.MakeDate(DeliveryDate)

Answer 6

Tienes que mantener limpio el lado izquierdo de tu cláusula where. Entonces, normalmente harías algo como:

WHERE MyDateTime >= @activityDateMidnight 
      AND MyDateTime < (@activityDateMidnight + 1)

(Algunas personas prefieren DATEADD (d, 1, @activityDateMidnight) en su lugar, pero es lo mismo).

Sin embargo, la tabla TimeZone complica un poco la cuestión. No está claro desde el fragmento, pero parece que t.TheDateInTable está en GMT con un identificador de zona horaria, y que luego está agregando el desplazamiento para comparar con @activityDateMidnight, que está en hora local. No estoy seguro de lo que ds.LocalTimeZone es, sin embargo.

Si ese es el caso, entonces necesita obtener @activityDateMidnight en GMT en su lugar.

Answer 7

Usaría la función dayofyear de datepart:

Select *
from mytable
where datepart(dy,date1) = datepart(dy,date2)
and
year(date1) = year(date2) --assuming you want the same year too

Vea la referencia de fecha y hora aquí .

Answer 8

esto eliminará el componente de tiempo de una fecha para usted:

select dateadd(d, datediff(d, 0, current_timestamp), 0)

Answer 9

where
year(date1) = year(date2)
and month(date1) = month(date2)
and day(date1) = day(date2)

Answer 10

Eric Z Beard:

la fecha de actividad está destinada a indicar la zona horaria local, pero no una específica

Bien, de vuelta al tablero de dibujo. Prueba esto:

where t.TheDateINeedToCheck BETWEEN (
    dateadd(hh, (tz.Offset + ISNULL(ds.LocalTimeZone, 0)) * -1, @ActivityDate)
    AND
    dateadd(hh, (tz.Offset + ISNULL(ds.LocalTimeZone, 0)) * -1, (@ActivityDate + 1))
)

que traducirá el @ActivityDate a la hora local y se comparará con eso. Esa es tu mejor oportunidad para usar un índice, aunque no estoy seguro de que funcione, deberías probarlo y verificar el plan de consulta.

La siguiente opción sería una vista indizada, con un TimeINeedToCheck calculado e indexado en hora local . Luego vuelves a:

where v.TheLocalDateINeedToCheck BETWEEN @ActivityDate AND (@ActivityDate + 1)

que definitivamente usaría el índice, aunque tiene una ligera sobrecarga en INSERT y UPDATE.