Solved

SQl Query Help Needed

Posted on 2014-04-17
7
402 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Solar Winds can't see SQL Server Express 17 32
check mysql insert 12 26
SSRS: Why is Visual Studio stripping these properties? 2 21
SQL 2012 clustering 9 11
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

861 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