The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

Date functions: calculate periods and build safe limits

Dates represent instants—specific points in time—or periods—intervals between two boundaries—not text with a visual format. We use temporal functions and suitable types to add days, measure differences, obtain the end of a month, or construct a date.

One of the most important decisions is to correctly express ranges when the column also contains time.

Date operations

DATEADD: shift a date

DATEADD adds an amount in the specified unit. A negative amount moves the date backward.

DATEADD(Unidad, Cantidad, Fecha)

The unit can be DAY, MONTH, or YEAR, among others. It is not written as a quoted string.

DATEDIFF: count crossed boundaries

DATEDIFF counts how many boundaries of the specified unit occur between a start and an end. It does not measure an exact fractional duration.

DATEDIFF(Unidad, FechaInicial, FechaFinal)

DATEFROMPARTS: construct a date

DATEFROMPARTS accepts year, month, and day as numbers and returns a DATE value. If the combination is not a valid date, the function raises an error.

DATEFROMPARTS(Anio, Mes, Dia)

EOMONTH: get the last day of a month

EOMONTH returns the date for the last day of the month containing the supplied value.

EOMONTH(Fecha)

SYSDATETIME: read the server’s local clock

SYSDATETIME returns the server’s date and time with DATETIME2 precision. It takes no arguments and does not automatically represent the user’s time zone.

SYSDATETIME()

Example: add a period

SELECT
    p.IdPedido,
    p.FechaPedido,
    DATEADD(DAY, 5, p.FechaPedido) AS FechaEstimada
FROM dbo.Pedido AS p;

The example moves each date five days forward to produce an estimated date.

Measure a difference

SYSDATETIME() takes the current time of the server’s local clock. It takes no arguments; The parentheses are part of the function call.

SELECT
    c.IdCliente,
    c.FechaRegistro,
    DATEDIFF(DAY, c.FechaRegistro, SYSDATETIME()) AS DiasDesdeRegistro
FROM dbo.Cliente AS c;

DATEDIFF counts how many day boundaries were crossed. It does not calculate an exact decimal duration. Two instants separated by a few seconds can produce 1 day if they fall on opposite sides of midnight.

Build the start and end of a month

DECLARE @Anio INT = 2026;
DECLARE @Mes INT = 3;

SELECT
    DATEFROMPARTS(@Anio, @Mes, 1) AS InicioMes,
    EOMONTH(DATEFROMPARTS(@Anio, @Mes, 1)) AS FinMes;

Variables created using DECLARE are explained separately in DECLARE, SET, and SELECT.

DATEFROMPARTS avoids depending on the language or regional format of a string.

Filter a range containing time

To retrieve every March order, we use an inclusive lower boundary, which admits the boundary value, and an exclusive upper boundary, which leaves it out:

DECLARE @Desde DATE = '2026-03-01';
DECLARE @Hasta DATE = '2026-04-01';

SELECT
    p.IdPedido,
    p.FechaPedido,
    p.Estado
FROM dbo.Pedido AS p
WHERE p.FechaPedido >= @Desde
  AND p.FechaPedido < @Hasta
ORDER BY p.FechaPedido;

This form includes any time precision within March. Avoid writing an ending like 23:59:59, which can skip values ​​with greater precision.

Calculate next month

When we only receive an initial date, the exclusive limit can be derived:

DECLARE @InicioMes DATE = '2026-03-01';
DECLARE @InicioSiguienteMes DATE = DATEADD(MONTH, 1, @InicioMes);

What should be checked

We must distinguish DATE, DATETIME, DATETIME2, and values with a zone or offset. An offset is the numerical difference from UTC attached to an instant. SYSDATETIME() uses the server’s local clock; it does not automatically represent the user’s zone.

We also avoid converting the column to text inside the filter. Comparing a temporal range preserves the meaning of the data and usually provides a better chance of using an index, a structure that organizes values to make rows easier to access.