Solved

SQL to get data between 2 date fields

Posted on 2014-12-05
14
336 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

726 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