Home / Questions / 99692
Accepted
Luis Pizarro Ramírez
Asked: 2020-09-05 06:26:29 +0800 CST 2020-09-05 06:26:29 +0800 CST

SQL Query between date range - but a date can be NULL

  • 772

Good morning community.

I need to make a query in SQL (Sql Server 2012) to filter by a range of dates (StartDate - EndDate).

The table is as follows:

ARRESTS

ID
ESTADO
EQUIPO
FECHAINICIO
FECHATERMINO

The Problem, is that sometimes the Field FECHATERMINOmay be NULLbecause it is still "stopped" to date according to my business logic.

SELECT * FROM DETENCIONES WHERE EQUIPO=1
AND @FECHA >= FECHAINICIO AND @FECHA <= FECHATERMINO

I already tried with an OR but it returns all the TEAMS that have a NULL in their end date, that is, it does not respect the parentheses.

SELECT * FROM DETENCIONES WHERE EQUIPO=@EQUIPO
AND @FECHA >= FECHAINICIO AND (@FECHA <= FECHATERMINO OR FECHATERMINO=NULL)

PS: I visualize the data in a Website created with ASP.NET C#.

c#

1 Answers

  1. Best Answer

    The simplest would be to use ISNULLwith a future date or GETDATE:

    SELECT *
FROM Detenciones
WHERE Equipo = 1
AND @Fecha BETWEEN FechaInicio AND ISNULL(FechaTermino,GETDATE());
    • 10