3

I have the following data and I don’t understand how the where clause works

CREATE TABLE ##TBL (ID  INT, StartDate DATE,    EndDate DATE)
 INSERT INTO ##TBL VALUES
 (1,'2018-01-01','2018-02-07'), (2,'2018-01-02','2018-09-17'),
 (3,'2018-01-03','2018-07-12'), (4,'2018-01-04','2018-07-03'),
 (5,'2018-01-05','2018-05-31'), (6,'2018-01-06','2018-07-28'),
 (7,'2018-01-07','2018-03-08'), (8,'2018-01-08','2018-08-05'),
 (9,'2018-01-09','2018-09-20'), (10,'2018-01-10','2018-08-14'),
 (11,'2018-01-11','2018-03-30'), (12,'2018-01-12','2018-03-02'),
 (13,'2018-01-13','2018-05-15'), (14,'2018-01-14','2018-03-14'),
 (15,'2018-01-15','2018-08-22'), (16,'2018-01-16','2018-04-09'),
 (17,'2018-01-17','2018-06-03'), (18,'2018-01-18','2018-09-30'),
 (19,'2018-01-19','2018-04-03'), (20,'2018-01-20','2018-02-14');

When I execute the query using the code below based on the date parameter, I don’t understand the result

DECLARE @RportDate DATE 
      SET @RportDate='2018-01-04'
 SELECT* FROM ##TBL
 WHERE @RportDate BETWEEN StartDate AND EndDate
 DROP TABLE ##TBL

For I example if I use the date '2018-01-04' for the parameter I have this result

ID  StartDate   EndDate
1   2018-01-01  2018-02-07
2   2018-01-02  2018-09-17
3   2018-01-03  2018-07-12
4   2018-01-04  2018-07-03

And if I change the date to '2018-01-02' The output will be 

ID  StartDate   EndDate
1   2018-01-01  2018-02-07
2   2018-01-02  2018-09-17

My question is does the query only filter for the startDate? Any explanation for this behaviour

JonWay
  • 1,585
  • 17
  • 37
  • 1
    What's so difficult to understand? The given date is in the StartDate & EndDate ranges it returns. What else were you expecting? – LukStorms Jan 15 '19 at 11:41
  • The date format is `yyyy-mm-dd`. For 2018-01-04, all records will be returned because it is in the range of startdate and enddate for all records. – shahkalpesh Jan 15 '19 at 11:42
  • Read Aaron Bertrand's [What do BETWEEN and the devil have in common?](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) – Zohar Peled Jan 15 '19 at 11:46
  • What did expect? Do you expect `SELECT * FROM Table WHERE @RportDate >= StartDate OR @RportDate <= EndDate`? – Ilyes Jan 15 '19 at 11:46
  • check this: `select dateformat from sys.syslanguages where name = @@LANGUAGE` Depending on your system's defaults. the literal `'2018-01-02'` let's me think, that you might be living in a YDM-country (*first of Feb*), while SQL-Server takes this as YMD (*second of Jan*) – Shnugo Jan 15 '19 at 11:47
  • @Shnugo considering that there is a value `'2018-08-14'` in the sample data *if* the OP's data engine was interpreting the values as `yyyy-dd-MM` they would get a failure. – Thom A Jan 15 '19 at 11:48
  • @Larnu, that's true, but we don't know how the sample data was generated/typed and how the literal is taken by the actual system. However: I'd avoid culture dependant literals in each and any case :-) – Shnugo Jan 15 '19 at 11:50
  • @Shnugo That I agree with. Why i stick to `yyyyMMdd` and `yyyy-MM-ddThh:mm:ss.sssssss`. No nasty surprisely. I just wish our software vendor would do the same. – Thom A Jan 15 '19 at 11:52
  • Meh, I think the yyyy-MM-dd is standard & safe enough for a date literal. – LukStorms Jan 15 '19 at 11:54
  • 1
    @LukStorms but it's not... `SELECT CONVERT( datetime,'2019-01-15')` fails on my server with the error `Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.` for the exact reason we talk about in the above comments. On the other hand `SELECT CONVERT(date,'2019-01-15')` works, and `SELECT CONVERT(date,'2019-01-12'), CONVERT(datetime,'2019-01-12');` returns 2 different values. That sort of inconsistent behaviour is a huge reason why `yyyy-MM-dd` is a bad choice. – Thom A Jan 15 '19 at 11:57
  • 2
    @LukStorms if you are using `Date` or `Datetime2` than yes. However, if you are using `DateTime`, it's **not** safe. See Shnugo's [post about it](https://stackoverflow.com/questions/45792766/date-conversion-and-culture-difference-between-date-and-datetime) – Zohar Peled Jan 15 '19 at 13:04
  • @Larnu, not exactly accurate. It's the reason why using `DateTime` instead of `DateTime2` is a bad choice. Read the post I've linked to in my comment to lukStorms. – Zohar Peled Jan 15 '19 at 13:06
  • @ZoharPeled Thanks for the link. Ok, I'll keep in mind that DATETIME is more quirky than DATE or DATETIME2. (won't stop me using it though ;) ) – LukStorms Jan 15 '19 at 13:10

1 Answers1

2

You can try the following query, it is the same as your query, but written this way will be easier to understand.

And it filters StartDate and EndDate.

DECLARE @RportDate DATE 
SET @RportDate='2018-01-04'

SELECT * FROM ##TBL
WHERE StartDate <= @RportDate 
    AND EndDate >= @RportDate

DROP TABLE ##TBL

You can find the live demo Live Demo Here

Pawel Czapski
  • 1,856
  • 2
  • 16
  • 26
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42