?
Solved

SQl Query Help Needed

Posted on 2014-04-17
7
Medium Priority
?
410 Views
Last Modified: 2014-04-23
How can I edit this SQL to return the order_date between October 13, 2013 - April 17, 2014?

$query="SELECT fid, order_date, due_date, employee FROM database1 WHERE employee = 'jane doe' AND active='1'";
0
Comment
Question by:wantabe2
[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
7 Comments
 
LVL 22

Accepted Solution

by:
plusone3055 earned 2000 total points
ID: 40006461
now it could also be writte nlike this for the dates

$query="SELECT fid, order_date, due_date, employee FROM database1 WHERE employee = 'jane doe' AND active='1'
AND order_date BETWEEN '10-13-13' AND  '4-17-14'";
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40006503
Only the above answer is correct, assuming employee and active are both character values (although active  = '1' will convert to numeric via implicit conversion.)

Also, where you have database1 should be a table name.

Also, for standard date settings, better to use yyyy-mm-dd.

$query="SELECT fid, order_date, due_date, employee FROM database1 WHERE employee = 'jane doe' AND active='1'
AND order_date BETWEEN '2013-10-13' AND  '2014-04-17'";

btw check out PortletPaul's article on Beware of Between to make sure you're handling the date boundaries correctly.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40006510
JH

I was trying to get my 1st SQL mark.... I wrote it too fast hehehe
I really think they should give us the option to DELETE our own comments when we make them in haste
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40006522
I just did you a favor and deleted the first three comments.
That's why I drink a lot of coffee in the morning
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40006540
I drink tap water mixed with lemon/lime/cucumber slices and mint leaves...
it tastes terrible but a good for muscle pain
0
 
LVL 2

Expert Comment

by:sarvjita
ID: 40006574
try this

$query="SELECT fid, order_date, due_date, employee FROM database1.table1 WHERE employee = 'jane doe' AND active='1' AND (convert(varchar(10),order_date,110) between convert(varchar(10),'13-10-2013',110) and convert(varchar(10),'17-04-2014',110))";
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40017389
The topics include both MySQL & MS SQL and the generic query syntax, so I'm a little unsure what is really wanted because date handling does differ greatly amongst the database vendors. So a totally generic answer isn't really possible

The accepted answer provides:

AND order_date BETWEEN '10-13-13' AND  '4-17-14'";

I do hope nobody really uses 2 digit years in production code :(

do, please, at least, use 4 digits for the year

@Jim, thanks for the reference to Beware of Between.
That article's title is both deliberate and meaningful. One really does have to be wary of using between for date ranges because queries can be just plain wrong if they use between poorly. I'm serious, just plain wrong is possible simply by using between.

The safest method for date ranges is a combination of >= with < like this:

AND ( order_date >= '2013-10-13' AND order_date < '2014-04-18' )";

note! I've used "less than 18th April 2014"
(so that it does not matter what time precision applies to the field order_date)


BTW:
In MS SQL Server the safest of all date literals is in fact YYYYMMDD (without any delimiter),

It's different for MySQL so I've used YYYY-MM-DD for this comment
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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, we’ll look at how to deploy ProxySQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

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