Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQl Query Help Needed

Posted on 2014-04-17
7
Medium Priority
?
431 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
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 50

Expert Comment

by:Paul
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

608 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