SQl Query Help Needed

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'";
LVL 15
wantabe2Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
plusone3055Connect With a Mentor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
plusone3055Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
plusone3055Commented:
I drink tap water mixed with lemon/lime/cucumber slices and mint leaves...
it tastes terrible but a good for muscle pain
0
 
sarvjitaSystem AdministratorCommented:
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
 
PortletPaulCommented:
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
All Courses

From novice to tech pro — start learning today.