Solved

SQL to get data between 2 date fields

Posted on 2014-12-05
14
330 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
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.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 39
mysql disables rename 4 67
SQL Help 27 42
SQL Server - Find Unmatched Records Between Two Tables - Has Trouble with Nulls 10 28
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

786 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