Solved

SQL to get data between 2 date fields

Posted on 2014-12-05
14
329 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
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 65

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
 

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 65

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 65

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 500 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 65

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

930 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now