Get records between two DATETIME in SQL Server

Navigation
1

I need to get the available records between two DATETIME columns in SQL Server, so that one column is the beginning and the other the end.
That is, when making a query based on both values, get the data that are in the range of the DATETIME, or that affect a part of that interval.

For example, make a query that with a start '2016-12-07 07:30' and end '2016-12-08 10:45' . Basically I need the query to return records that meet the following cases:

  • Records that start and end within the interval.
  • Records that start before the start of the interval but end within the same.
  • Records that start after the start of the interval and that finish after the end of the interval.
  • Records that start before the start of the interval, and end | after the end of the interval.

Basically, the goal is records that are contained at least partially or completely.
I have used the following query, but it does not work in all cases: 

SELECT * FROM TABLA WHERE (VALOR_INICIO BETWEEN INICIO AND FINAL)
OR (VALOR_FINAL BETWEEN INICIO AND FINAL);
    
asked by migueladanrm 08.12.2016 в 06:16
source

2 answers

1

Assuming that inicio and final refer to the columns in your table, and valor_inicio and valor_final refer to the parameters that define the interval you are looking for, then you can condense all your requirements with the next sentence: 

-- @valor_inicio = '2016-12-07 07:30'
-- @valor_final  = '2016-12-08 10:45'
select *
  from tabla t
 where (@valor_inicio >= t.inicio or @valor_final >= t.inicio)
   and (@valor_inicio <= t.final or @valor_final <= t.final)

Edit

I just realized that the query can be further simplified. The following query is enough: 

select *
  from tabla t
 where @valor_final >= t.inicio
   and @valor_inicio <= t.final
    
answered by 08.12.2016 / 07:31
source
1

I think you need the following script: 

USE basededatos

declare @startDate datetime
set @startDate = '2016-11-01 09:10'

declare @endDate datetime
set @endDate = '2016-11-01 18:00'

-- Registros que empiecen y finalicen dentro del intervalo.
select * from TABLA where (COL_INICIO between @startDate and @endDate) 
AND (COL_FINAL between @startDate and @endDate)

-- Registros que empiecen antes del inicio del intervalo pero finalicen dentro del mismo.
select * from TABLA where COL_INICIO < @startDate and 
(COL_FINAL between @startDate and @endDate)

--Registros que empiecen después del inicio del intervalo y que terminen después del final del intervalo.
select * from TABLA where COL_INICIO > @startDate and 
(COL_FINAL > @endDate)

-- Registros que empiecen antes del inicio del intervalo, y finalicen
después del final del intervalo.
select * from TABLA where COL_INICIO < @startDate and 
(COL_FINAL > @endDate)

It can serve you to declare (DECLARE) variables so that the dates do not write them in each SELECT (if it is a single script), to make the filtering of dates in SQL Server, the comparison operators serve you as: "<" , ">", "<=", ">="

Comparison operators: link

    
answered by 08.12.2016 в 07:03
Does not load CSS on NodeJS server Doubt to create mysql table with monthly and quarterly values