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 >= '2019-02-09' AND reading_start_dt <= '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.
Advertisement
Answer
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