如何在fromdate和todate之间获取数据 [英] How to get data between fromdate and todate

问题描述

  DECLARE   @ tb   TABLE （Id  INT   IDENTITY （ 1 ， 1 ），EmployeeId  INT ，LeaveFromDate  DATE ，LeaveToDate  DATE ）
 
  INSERT   INTO   @ tb   VALUES （ 101 ，'  2018-01-03'，  2018-01-05'）
  INSERT   INTO   @ tb   VALUES （ 101 ，'  2018-01-07'， '  2018-01-14'）
  INSERT   INTO   @ tb   VALUES （ 101 ，'  2018-01-15'，'  2018-01-15'）
  INSERT   INTO   @ tb   VALUES （ 101 ，'  2018-01 -18'，'  2018-01-25'）
  INSERT   INTO   @ tb   VALUES （ 102 ，'  2018-01-04'，' < span class =code-string> 2018-01-07'）
  INSERT   INTO   @ tb   VALUES （ 102 ，'  2018-01-11'，'  2018-01-16'）
  INSERT   INTO   @ tb   VALUES （ 102 ，'  2018-01-18'，'  2018-01-20'）
 
  SELECT  *  FROM   @tb  
 
 
  DECLARE   @ StartDate   DATE ， @ EndDate   DATE ，< span class =code-sdkkeyword> @ EmployeeId   INT ; 
 
  SET  @ StartDate = '  2018年1月4' 日; 
  SET  @ EndDate = '  2018-01 -05 ; 
  SET  @ EmployeeId = 101; 
  SELECT  *  FROM   @ tb   WHERE  EmployeeId = @ EmployeeId  AND （（LeaveFromDate  BETWEEN   @ StartDate   AND   @ EndDate  ） OR （LeaveToDate  BETWEEN   @ StartDate  < span class =code-keyword> AND   @ EndDate ））

显示正确的结果;

但在以下情况下，它不会返回结果。

  SET  @ StartDate = '  2018-01-04'; 
  SET  @ EndDate = '  2018-01 -04 ; 
  SET  @ EmployeeId = 101; 
  SELECT  *  FROM   @ tb   WHERE  EmployeeId = @ EmployeeId  AND （（LeaveFromDate  BETWEEN   @ StartDate   AND   @ EndDate  ） OR （LeaveToDate  BETWEEN   @ StartDate  < span class =code-keyword> AND   @ EndDate ））

它应该显示id = 101的第一条记录，因为'2018-01-04'介于'2018-01-03'和'2018-01-05'之间



怎么弄这个？请帮助



我尝试过的事情：

  SELECT  *  FROM   @ tb   WHERE  EmployeeId = @ EmployeeId  AND （（LeaveFromDate  BETWEEN   @ StartDate   AND   @ EndDate  ） OR （LeaveToDate  BETWEEN   @ StartDate  < span class =code-keyword> AND   @ EndDate ））

解决方案

它确实：

 Id EmployeeId LeaveFromDate LeaveToDate 
 1 101 2018-01-03 2018-01-05 
 2 101 2018-01-07 2018-01-14 
 3 101 2018-01-15 2018-01-15 
 4 101 2018-01-18 2018-01-2 5 
 5 102 2018-01-04 2018-01-07 
 6 102 2018-01-11 2018-01-16 
 7 102 2018-01-18 2018-01-20 

 Id EmployeeId LeaveFromDate LeaveToDate 
 1 101 2018-01-03 2018-01-05 

那么什么是问题

DECLARE @tb TABLE (Id INT IDENTITY(1,1), EmployeeId INT, LeaveFromDate DATE, LeaveToDate DATE)

INSERT INTO @tb  VALUES (101,'2018-01-03','2018-01-05')
INSERT INTO @tb  VALUES (101,'2018-01-07','2018-01-14')
INSERT INTO @tb  VALUES (101,'2018-01-15','2018-01-15')
INSERT INTO @tb  VALUES (101,'2018-01-18','2018-01-25')
INSERT INTO @tb  VALUES (102,'2018-01-04','2018-01-07')
INSERT INTO @tb  VALUES (102,'2018-01-11','2018-01-16')
INSERT INTO @tb  VALUES (102,'2018-01-18','2018-01-20')

SELECT * FROM @tb


DECLARE @StartDate DATE,@EndDate DATE,@EmployeeId INT;

SET @StartDate='2018-01-04';
SET @EndDate='2018-01-05';
SET @EmployeeId=101;
SELECT * FROM @tb WHERE EmployeeId=@EmployeeId AND  ((LeaveFromDate BETWEEN @StartDate AND @EndDate) OR (LeaveToDate BETWEEN @StartDate AND @EndDate))

It is displaying proper result;
But in the following case it is not returning result.

SET @StartDate='2018-01-04';
SET @EndDate='2018-01-04';
SET @EmployeeId=101;
SELECT * FROM @tb WHERE EmployeeId=@EmployeeId AND  ((LeaveFromDate BETWEEN @StartDate AND @EndDate) OR (LeaveToDate BETWEEN @StartDate AND @EndDate))

It should display the very first record with id=101 because '2018-01-04' is in between '2018-01-03' and '2018-01-05'

How to get this? Please help

What I have tried:

SELECT * FROM @tb WHERE EmployeeId=@EmployeeId AND  ((LeaveFromDate BETWEEN @StartDate AND @EndDate) OR (LeaveToDate BETWEEN @StartDate AND @EndDate))

解决方案

And it does:

Id	EmployeeId	LeaveFromDate	LeaveToDate
1	101	2018-01-03	2018-01-05
2	101	2018-01-07	2018-01-14
3	101	2018-01-15	2018-01-15
4	101	2018-01-18	2018-01-25
5	102	2018-01-04	2018-01-07
6	102	2018-01-11	2018-01-16
7	102	2018-01-18	2018-01-20

Id	EmployeeId	LeaveFromDate	LeaveToDate
1	101	2018-01-03	2018-01-05

So what's the problem?

这篇关于如何在fromdate和todate之间获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！