SQL Server如何将时间取整至整点？附现有查询语句

Hey there! Let's adjust your existing SQL query to round the FechaHora values to the nearest hour. There are two common scenarios you might need: truncating to the previous hour (dropping minutes/seconds entirely) or rounding to the closest hour (1:30 becomes 2:00, 1:29 stays 1:00). Here's how to implement both:

1. Truncate to the Exact Hour (Floor the Time) #

This method strips all minutes and seconds, setting the time to the start of the current hour. Replace your existing cast(mo.FechaHora as time) calls with this expression:

CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, mo.FechaHora), 0) AS TIME)

Modified Full Query #

SELECT 
    e.Cedula,
    CONCAT(e.Nombre, ' ', e.Apellido) AS nombre,
    C.Descripcion AS cargo,
    MAX(CASE WHEN mo.Sentido = 'Entrada' THEN CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, mo.FechaHora), 0) AS TIME) END) AS Entrada,
    MAX(CASE WHEN mo.Sentido = 'Salida' THEN CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, mo.FechaHora), 0) AS TIME) END) AS Salida,
    e.Direccion AS observaciones
FROM mambo.dbo.EMPLEADO e
LEFT JOIN mambo.dbo.CARGO c ON e.IdCargo = c.IdCargo
LEFT JOIN mambo.dbo.MARCACIONES_PARA_LIQUIDACION mo 
    ON e.IdEmpleado = mo.IdEmpleado 
    AND CAST(mo.FechaHora AS DATE) = '201...' -- Replace with your full target date
GROUP BY e.Cedula, CONCAT(e.Nombre, ' ', e.Apellido), C.Descripcion, e.Direccion;

2. Round to the Nearest Hour (四舍五入) #

If you want to round to the closest hour (e.g., 9:30 AM becomes 10:00 AM, 9:29 AM stays 9:00 AM), first shift the time forward by 30 minutes before truncating:

CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(MINUTE, 30, mo.FechaHora)), 0) AS TIME)

Modified Full Query for Rounding #

SELECT 
    e.Cedula,
    CONCAT(e.Nombre, ' ', e.Apellido) AS nombre,
    C.Descripcion AS cargo,
    MAX(CASE WHEN mo.Sentido = 'Entrada' THEN CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(MINUTE, 30, mo.FechaHora)), 0) AS TIME) END) AS Entrada,
    MAX(CASE WHEN mo.Sentido = 'Salida' THEN CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(MINUTE, 30, mo.FechaHora)), 0) AS TIME) END) AS Salida,
    e.Direccion AS observaciones
FROM mambo.dbo.EMPLEADO e
LEFT JOIN mambo.dbo.CARGO c ON e.IdCargo = c.IdCargo
LEFT JOIN mambo.dbo.MARCACIONES_PARA_LIQUIDACION mo 
    ON e.IdEmpleado = mo.IdEmpleado 
    AND CAST(mo.FechaHora AS DATE) = '201...' -- Replace with your full target date
GROUP BY e.Cedula, CONCAT(e.Nombre, ' ', e.Apellido), C.Descripcion, e.Direccion;

Quick Breakdown of the Logic #

• DATEDIFF(HOUR, 0, mo.FechaHora): Counts how many full hours have passed since SQL Server's epoch (1900-01-01 00:00:00) to your datetime value.
• DATEADD(HOUR, ..., 0): Adds that hour count back to the epoch, resulting in a datetime truncated to the start of the hour.
• For rounding, DATEADD(MINUTE, 30, mo.FechaHora) pushes times in the second half of the hour into the next hour before truncation, effectively rounding up.

Just remember to fill in your full target date in place of '201...' and keep the GROUP BY clause (required since you're using aggregate functions like MAX).

内容的提问来源于stack exchange，提问作者Jose