?
Solved

SQL to get data between 2 date fields

Posted on 2014-12-05
14
Medium Priority
?
337 Views
Last Modified: 2014-12-07
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...
0
Comment
Question by:imranasif17
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40483295
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
 
LVL 24

Expert Comment

by:mankowitz
ID: 40483296
select * from A join B on (A.order_num=B.order_num)
where DATEDIFF ( day, b.order_date, b.delivery_date) <= 1
0
 

Author Comment

by:imranasif17
ID: 40483379
I am getting error:

Error Code: 1582. Incorrect parameter count in the call to native function 'DATEDIFF'
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

Author Comment

by:imranasif17
ID: 40483396
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40483550
>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
 

Author Comment

by:imranasif17
ID: 40483651
Thanks, Jim.  I am getting the same error.  I am using MS-SQL.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40483669
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
 
LVL 24

Accepted Solution

by:
mankowitz earned 2000 total points
ID: 40483692
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
 
LVL 18

Expert Comment

by:Simon
ID: 40483781
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
 

Author Closing Comment

by:imranasif17
ID: 40483934
Thanks everyone for the help.  The solution works.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40483942
So out of curiosity, were you looking for a MySQL solution and not a SQL Server solution?
0
 

Author Comment

by:imranasif17
ID: 40484147
Yes, sorry my bad. Thanks, Jim
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40485644
@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

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question