Hey there! Let's walk through exactly how Teradata handles date subtraction when the resulting difference falls between -3 and 3 days. This is a common scenario for filtering nearby dates, so it's good to get the details right.

Core Behavior of Date Subtraction #

First off, in Teradata, subtracting two DATE type values (Date1 - Date2) returns an integer representing the number of full days between the two dates:

• A positive number means Date1 is later than Date2 (e.g., DATE '2024-05-12' - DATE '2024-05-10' returns 2, since 12 is 2 days after 10)
• A negative number means Date1 is earlier than Date2 (e.g., DATE '2024-05-08' - DATE '2024-05-10' returns -2, since 08 is 2 days before 10)
• A 0 means the two dates are identical

How the -3 to 3 Range Works #

When you check if (Date1 - Date2) falls between -3 and 3, Teradata treats this as a closed interval—meaning it includes the exact values -3, 3, and every integer in between (-2, -1, 0, 1, 2).

In plain terms, this condition will be true if:

• Date1 is exactly 3 days earlier than Date2
• Date1 is 2 days earlier than Date2
• Date1 is 1 day earlier than Date2
• Date1 and Date2 are the same day
• Date1 is 1 day later than Date2
• Date1 is 2 days later than Date2
• Date1 is exactly 3 days later than Date2

Practical Examples #

Let's look at some concrete SQL snippets to make this clear:

1. This returns -3 (matches the lower bound):

   SELECT DATE '2024-05-07' - DATE '2024-05-10' AS date_diff;
   

2. This returns 3 (matches the upper bound):

   SELECT DATE '2024-05-13' - DATE '2024-05-10' AS date_diff;
   

3. A filter condition to find orders where the order date and ship date are within 3 days of each other:

   SELECT order_id, order_date, ship_date
   FROM orders
   WHERE (order_date - ship_date) BETWEEN -3 AND 3;
   

   This will grab all orders where the ship date is up to 3 days before the order date, up to 3 days after, or on the same day.

Key Notes to Remember #

• Teradata only calculates full-day differences for DATE types—you won't get fractional days here. If you're working with TIMESTAMP values, you'll need to cast them to DATE first (e.g., CAST(timestamp_col AS DATE)) to get this integer day difference.
• The BETWEEN operator is inclusive in Teradata, so don't forget that -3 and 3 are both included in the range.

内容的提问来源于stack exchange，提问作者Bhavini