Solved

SQl Query Help Needed

Posted on 2014-04-17
7
397 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 500 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 65

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 65

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 48

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now