SQL to get data between 2 date fields

Hi Experts:

I need help with an SQL query.  I have 2 tables:

Table A, Table B

Table A columns are - order #, Item
Table B columns are - order #, order_date, delivery_date

I want to get all orders/items that are delivered within 1 day of the order date.

Eg: Only return values, If the order date is December 1st, 2014, the delivery date is December 2nd, 2014

Thanks in advance...
imranasif17Asked:
Who is Participating?
 
mankowitzCommented:
wait, are you using MySQL? That error sounds more mysql-ish. Tell me if this works:

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  timestampdiff(day, b.ord_dt, b.dlv_dt) <= 1;
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
 
mankowitzCommented:
select * from A join B on (A.order_num=B.order_num)
where DATEDIFF ( day, b.order_date, b.delivery_date) <= 1
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
imranasif17Author Commented:
I am getting error:

Error Code: 1582. Incorrect parameter count in the call to native function 'DATEDIFF'
0
 
imranasif17Author Commented:
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;
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
0
 
imranasif17Author Commented:
Thanks, Jim.  I am getting the same error.  I am using MS-SQL.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
 
SimonCommented:
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;
0
 
imranasif17Author Commented:
Thanks everyone for the help.  The solution works.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So out of curiosity, were you looking for a MySQL solution and not a SQL Server solution?
0
 
imranasif17Author Commented:
Yes, sorry my bad. Thanks, Jim
0
 
mankowitzCommented:
@simon: caution with datediff - it only looks at the date portion of the datetime, not the whole thing, so if you have two timestamp separated by midnight, it will always report an extra day.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.