problem in sql join query with between and where clause

Question

0.00/5 (No votes)

See more:

i am having problem with sql query. The query is as below

SQL

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID where OrderDetails.PurchaseDate between '1/4/2012' and '1/4/2012'

however this fallowing query works fine and shows results. but with where clause and between command in sql query gives no results the table is empty i have a date in purchasdate colum of OrdersDetails table which is 1/4/2012. y data is not displayed in table

SQL

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID

C#

public void bind()
    {
               string query = "SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID where OrderDetails.PurchaseDate Between '" + DropDownList1.Text + "' and '" + DropDownList2.Text + "'";
        DataSet ds = obj.fillgrid(query);
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
    }

Posted 4-Jan-12 3:18am

codegeekalpha

Updated 4-Jan-12 3:33am

v4

Add a Solution

Comments

Prasad_Kulkarni 4-Jan-12 9:20am

how you have executed your query using where clause can u post that code??

codegeekalpha 4-Jan-12 9:33am

i am testing this query in sqlserver. in code i am fetching date in dropdownlist from database and i am using where clause with dropdown list, question updated

devbtl 4-Jan-12 10:21am

check date format in dropdown.

Corporal Agarn 4-Jan-12 11:25am

do you see the data it you use '1/5/2012' as the second date? There may be a problem with the default date format mm/dd versus dd/mm.

codegeekalpha 4-Jan-12 11:37am

no problem with that...

devbtl 4-Jan-12 12:21pm

execute your query into database, if it returns correct data then debug your program in vs and see what is coming into your query. Copy query into query analyzer and see what is going on

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2012-01-04T09:06:00

First of all, don't use literals, always use SqlParameter[^]. This will help you with conversion problems and protect against SQL injections.

If the problem is the time portion, you can either define the column in the database as date if you don't need the time potion at all or if you need it, then one way is to eliminate it in your query. For the latter the querty could look something like:

SQL

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost 
FROM Products LEFT JOIN OrderDetails 
ON Products.ProductID=OrderDetails.ProductID 
WHERE  CAST(OrderDetails.PurchaseDate AS date) BETWEEN '1/4/2012' and '1/4/2012'

Or actually when parameters are correctly used, it would be:

SQL

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost 
FROM Products LEFT JOIN OrderDetails 
ON Products.ProductID=OrderDetails.ProductID 
WHERE  CAST(OrderDetails.PurchaseDate AS date) BETWEEN @startDate and @endDate

ARBebopKid · Answer 2 · 2012-01-04T06:14:00

Solution 1

It is converting '1/4/2012' to '1/4/2012 00:00'
So you don't have any between '1/4/2012 00:00' and '1/4/2012 00:00'

Change your second date to '1/5/2012'

Posted 4-Jan-12 6:14am

ARBebopKid

unclejimbob · Answer 3 · 2012-01-06T01:09:00

I see a couple of problems:
1) It is always a good idea to be completely unambiguous with datetimes

One way is to use the standard SQLS unseparated format 'yyyymmdd hh:mm:ss.mmm' to define your dates

e.g.

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID where OrderDetails.PurchaseDate between '20120104' and '20120104 23:59:59.999'

(or between '20120104 00:00:00.000' and '20120104 23:59:59.999' if you want to make it obvious that the first date is midnight of 3rd Jan 2012)

If the time component isn't significant then there is always the option of ignoring it...

where cast(datediff(day,0,OrderDetails.PurchaseDate) as datetime) between cast(datediff(day,0,'20120104') as datetime) and cast(datediff(day,0,'20120104') as datetime) will return all rows with a date sometime on the 4th

2) The query itself is suspect, the where clause is not executed as part of the left join. Therefore if you have no order details with a matching purchase date then you will also not see the product details.

If you wish to see the product details irrespective of whether the order details appear then the query needs to change to something like this (includes all rows with a date on the 4th or 5th Jan 2012):

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID and cast(datediff(day,0,OrderDetails.PurchaseDate) as datetime) between cast(datediff(day,0,'20120104') as datetime) and cast(datediff(day,0,'20120105') as datetime)

pguimalan · Answer 4 · 2012-01-09T18:07:00

what is the datatype of your field "PurchaseDate "?

i think it is DateTime, so the query below should be working:

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID where OrderDetails.PurchaseDate between '1/4/2012 00:00:00' and '1/4/2012 23:59:59'

Sreedhar Kadiyala · Answer 5 · 2012-01-04T07:44:00

Solution 2

Try this
The date we are passing is in 'dd-MM-yyyy' format

SQL

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost 
FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID 
where OrderDetails.PurchaseDate BETWEEN CONVERT(DATETIME,'01-04-2011',105) AND CONVERT(DATETIME,'01-04-2012',105)

Posted 4-Jan-12 7:44am

Sreedhar Kadiyala

Updated 4-Jan-12 8:57am

v2

Comments

Corporal Agarn 4-Jan-12 14:01pm

where OrderDetails.PurchaseDate BETWEEN CAST(DATETIME,'01-04-2011',105) AND CONVERT(DATETIME,'01-04-2012',105)+ 1

or somehow make the date midnight (see change by USABebopKid)

Sreedhar Kadiyala · Answer 6 · 2012-01-04T07:53:00

Sorry, Try this. It will work

SQL

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost 
FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID 
where CONVERT(DATETIME,CONVERT(VARCHAR,OrderDetails.PurchaseDate,105),105) BETWEEN CONVERT(DATETIME,'01-04-2012',105) AND CONVERT(DATETIME,'01-04-2012',105)

problem in sql join query with between and where clause

6 solutions

Solution 4

Solution 1

Solution 6

Solution 7

Solution 2

Solution 3

Add your solution here

Preview 0