CODEWITHSUNDEEP
sqlteradatateradata-sql-assistanthana-sql-scripthana-studio
user69355
user69355

Reputation: 109

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.

Upvotes: 1

Views: 717

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

Related Questions