asked on

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!

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ttist25

ASKER

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!