T-SQL Date Calculations

Hello,

I need to do some record matching based on two date fields from two tables.  

For example, I need to identify rows where the TABLE_B.ARRIVAL_DATE is on the same or 1 day greater than the TABLE_A.SHIP_DATE.

I've seen examples using BETWEEN with DATEADD(dd, -1) and eyeballing the results it seems to work correctly, but I've also seen recommendations against BETWEEN due to issues related to time.  My date fields don't contain times and for now I'm just concerned about the same day or +1.  

What is the best (and most simple) way to do this in SQL Server 2012?

Thanks!
LVL 1
ttist25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>I need to identify rows where the TABLE_B.ARRIVAL_DATE is on the same or 1 day greater than the TABLE_A.SHIP_DATE.

<total air code>

SELECT a.goo, a.boo, a.foo, b.yabba, b.dabba, b.doo, a.SHIP_DATE, b.ARRIVAL_DATE
FROM TABLE_A a 
   JOIN Table_B b ON {whatever relates these two tables}
WHERE DATEDIFF(day, a.SHIP_DATE, b.ARRIVAL_DATE) <= 1

-- this would probably run faster
WHERE a.SHIP_DATE = b.ARRIVAL_DATE OR a.SHIPDATE + 1 = b.ARRIVAL_DATE

Open in new window


>I've also seen recommendations against BETWEEN due to issues related to time
Check out Beware of Between for some guidance there.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
Since you have dates, you can compute next days date, too and use between Dates and Dates shifted by whole days with DateAdd. Dates behave with day precision like integers do for whole numbers.

Unless you have datetimes with 0:00 AM time portions, these are datetimes anyway.

So what are your column types?

Declare @sampledata as Table (Arrival_Date Date, Ship_Date Date)

Insert Into @sampledata values
('20180220','20180220'),
('20180221','20180220'),
('20180222','20180220')

Select * From @sampledata where Arrival_Date 
   Between Ship_Date and DateAdd(dd,1,Ship_Date)

Select *, CASE WHEN Arrival_Date Between Ship_Date and DateAdd(dd,1,Ship_Date) 
   THEN 1 ELSE 0 END as Within1Day From @sampledata

Open in new window


You can't compute Ship_Date+1 in that case, though. You get "Operand type clash: date is incompatible with int", when you try.

Bye, Olaf.
PortletPaulEE Topic AdvisorCommented:
If the columns are date only then between can safely be used for this requirement, but in my view it is always better to be explicit

-- using exists (will avoid any multiplication of rows, but only returns data from tableB)
select b.*
from tableB b
where exists (
    select null
    from tableA a
    where a.SHIP_DATE = b.ARRIVAL_DATE OR dateadd(day,1,a.SHIPDATE) = b.ARRIVAL_DATE
    )

Another method would be to use cross apply:

-- using cross apply
select b.*, ca.*
from tableB b
cross apply (
    select a.*
    from tableA a
    where a.SHIP_DATE = b.ARRIVAL_DATE OR dateadd(day,1,a.SHIPDATE)  = b.ARRIVAL_DATE
    ) ca
ttist25Author Commented:
Thanks guys and sorry for the delay in response.  Weekend, travel, blah , blah, blah.  

I ended up using DATEDIFF in my where clause:
WHERE 
		DATEDIFF(day, a.SHIP_DATE, b.ARRIVAL_DATE) <= 1 
		AND DATEDIFF(day, a.SHIP_DATE, b.ARRIVAL_DATE) > -1

Open in new window

I added the second part to deal with illogical dates (arrival dates that occur prior to the shipping date).  

Seems to have worked well.  Thanks again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.