SQL Date Range Specification, Explicit vs BETWEEN()

Question

My understanding of SQL is that

WHEN reading_start_dt BETWEEN '2020-02-09' AND '2020-02-15'

is the same as

WHEN reading_start_dt >= '2019-02-09' AND reading_start_dt <= '2019-02-15'

But I'm getting completely different ResultSets when the only difference in the queries is the date range specification.
The correct dates are being returned but the data is different.
This is happening in both Teradata and HANA.

Am I missing something?

EDIT:


SELECT meter_id, reading_meas, reading_interval_start_dttm FROM "_SYS_BIC"."NGDW.SM.PVT/METER_READING"  
WHERE Reading_Start_Dt BETWEEN '2020-02-09' AND '2020-02-15' AND Service_Channel_num = 1 AND Meter_id = 11870690   
ORDER BY Reading_Interval_Start_Dttm


SELECT meter_id, reading_meas, reading_interval_start_dttm FROM "_SYS_BIC"."NGDW.SM.PVT/METER_READING"  
WHERE reading_start_dt &gt= '2019-02-09' AND reading_start_dt &lt= '2019-02-15' AND service_channel_num = 1 AND meter_id = 11870690  
ORDER BY Reading_Interval_Start_Dttm

AS POINTED OUT THIS IS THE RESULT OF A TYPO IN MY CODE
I was comparing something in production with a requested change. Thank you for the sharp eyes, my eyes obviously too tired on a Saturday to see it.

*The correct dates are being returned but the data is different.*: what do you mean? A minimal reproducible example might be useful here to demonstrate your problem. — GMB, Feb 22 '20 at 20:54
Screenshot of the relevant datas please, and make sure the result sets are orderby the same thing, it's a stable orderby (unique) and there aren't a billion rows of results ; just 10 to demo the point will be fine — Caius Jard, Feb 22 '20 at 21:06
Your years are different (2020 in the first code snippet and 2019 in the second). Or is that just a typo? — Isaac, Feb 22 '20 at 21:34

score 3 · Accepted Answer · answered Feb 23 '20 at 01:43

3

Your date ranges are NOT the same, regardless of query syntax used

'2020-02-09' AND '2020-02-15' -- the year is 2020
'2019-02-09' AND '2019-02-15' -- the year here is 2019

These are syntactically equivalent:

WHEN reading_start_dt BETWEEN '2019-02-09' AND '2019-02-15'

WHEN reading_start_dt >= '2019-02-09' AND
     reading_start_dt <= '2019-02-15'

But the safest method of describing a date range is to use a combination of >= with < as in:

WHEN reading_start_dt >= '2019-02-09' AND
     reading_start_dt < '2019-02-16' -- note the increase of date here

This latter method works accurately regardless of how precise the time data is

answered Feb 23 '20 at 01:43

Paul Maxwell

33,002
3
32
51

Remark: the safe way (according to Standard SQL) to write a date is a *date literal*, supported by both Teradata and HANA: `BETWEEN DATE '2019-02-09' AND`. This requires `YMD` format and thus avoids possible conversion errors. – dnoeth Feb 23 '20 at 10:06
Thanks, great point regarding use of date literals. I was thinking more about the date range construction, and neither Hana nor Teradata are specialties of mine. – Paul Maxwell Feb 24 '20 at 11:21

score 1 · Answer 2 · answered Feb 22 '20 at 22:15

The correct dates are being returned but the data is different.

You make it sounds like the data is the same but the result sets have different ordering. Unless your query has an order by clause, then the data can be returned in any order.

These two filters are exactly the same:

WHEN reading_start_dt BETWEEN '2020-02-09' AND '2020-02-15'

WHEN reading_start_dt >= '2019-02-09' AND
     reading_start_dt <= '2019-02-15'

However, I would recommend writing the logic as:

WHEN reading_start_dt >= '2019-02-09' AND
     reading_start_dt < '2019-02-16'

This version works both for dates and date/time values.

SQL Date Range Specification, Explicit vs BETWEEN()

2 Answers2