sqlteradatateradata-sql-assistanthana-sql-scripthana-studio

SQL Date Range Specification, Explicit vs BETWEEN()

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

enter image description here


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

enter image description here

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.

Solution

  • 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