SQL to get data between 2 date fields

12/7/2014

12/5/2014

For starters, use DateDiff and something like the below code...

SELECT b.OrderNumber, a.Item, b.OrderDate, b.DeliveryDate
FROM TableB b
  JOIN TableA a ON b.OrderNumber = a.OrderNumber
WHERE DATEDIFF(hour, b.OrderDate, b.DeliveryDate) BETWEEN 0 AND 24

Open in new window

12/5/2014

select * from A join B on (A.order_num=B.order_num)
where DATEDIFF ( day, b.order_date, b.delivery_date) <= 1

12/5/2014

ASKER

I am getting error:

Error Code: 1582. Incorrect parameter count in the call to native function 'DATEDIFF'

12/5/2014

ASKER

Here is my query:

select * from tableA a
inner join tableB b on a.ORD_NBR=b.ORD_NBR
where b.HDR_STAT_CD in ('F') and b.PMT_CD<>'B'
and b.ord_dt between '2013-07-01 00:00:00' and '2014-06-30 00:00:00'
and b.dlv_dt = DATEDIFF(day, b.ord_dt, b.dlv_dt) <= 1;

12/5/2014

>and b.dlv_dt = DATEDIFF(day, b.ord_dt, b.dlv_dt) <= 1;

And something equals some number less than or equal to 1
Suggest you rewrite the above line.

Perhaps you mean...
AND DATEDIFF(day, b.ord_dt, b.dlv_dt) <= 1

12/5/2014

ASKER

Thanks, Jim. I am getting the same error. I am using MS-SQL.

12/5/2014

Which version? The below mockup code works on my 2012 box:

 Declare @dt_start datetime = '2014-12-01 08:30:00', @dt_end datetime = getdate()
 
SELECT
	@dt_start, 
	@dt_end, 
	DATEDIFF (hour, @dt_start, @dt_end) as hours_diff

Open in new window

ASKER CERTIFIED SOLUTION

12/5/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Simon

12/5/2014

It looks like you're using MySQL, rather than SQL Server.

The datediff function is different in MySQL. It simply counts number of days between two dates and you need to have the later date as the first parameter...
where datediff(delivery_date,order_date)<=1;

12/5/2014

ASKER

Thanks everyone for the help. The solution works.

12/5/2014

So out of curiosity, were you looking for a MySQL solution and not a SQL Server solution?

12/6/2014

ASKER

Yes, sorry my bad. Thanks, Jim