A problem with a query ?

Question

0.00/5 (No votes)

See more:

This Query it doesn't work it gives me no Data could you have a look please and tell me where is the erro ?
I will be glad for an explanation

What I have tried:

SQL

DECLARE @fromdate datetime = Convert (datetime, '05.10.19', 4), @todate datetime= Convert (datetime, '15.11.19', 4)

select  itemH.ProduktID,p.P_NavArtNr as ProduktNavArtNr,p.P_Name as ProduktName,

Concat(lager.Scancode ,handling.Scancode) as ToLocationScancode, 

Concat (lager2.Scancode ,handling2.Scancode) as FromLocationScancode, itemH.Date, itemH.Art

from ItemHistory itemH

join Produkte p on itemH.ProduktID = p.P_ID
left Join Lagerort lager on itemH.ToLocation = lager.LagerID

left join HandlingUnits handling on itemH.ToLocation = handling.Unit_ID

left join Lagerort lager2 on itemH.FromLocation = lager2.LagerID 

left join HandlingUnits handling2 on itemH.FromLocation = handling2.Unit_ID

where Date = @fromdate and Date =@todate

order by itemH.Date desc

Posted 2-Dec-19 3:33am

Abdalla Ben Omran

Updated 2-Dec-19 21:08pm

Herman<T>.Instance

v2

Add a Solution

Comments

[no name] 2-Dec-19 9:41am

WHERE Date = @fromdate and Date =@todate
should be more
WHERE Date >= @fromdate and Date <= @todate

[no name] 2-Dec-19 10:00am

thanks it works as i wanted ..many thanks

[no name] 2-Dec-19 10:02am

You are welcome. Maybe you accept SOlution 1, it is equivalent ;)

[no name] 2-Dec-19 10:05am

you didn't sumbit that as Solution .. could you edit your answer please . i will accept it as solution

[no name] 2-Dec-19 10:10am

Done

2 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)

OriginalGriff · Accepted Answer · 2019-12-02T03:45:00

Solution 1

Unless your "valid period" is very short indeed, this condition will never be met:

SQL

where Date = @fromdate and Date =@todate

Additionally, "=" is an exact match - if you are using DATETIME values then the time portion must also match to the tick.
I'd suggest you actually want this:

SQL

WHERE [Date] BETWEEN @fromdate AND @todate

Posted 2-Dec-19 3:45am

OriginalGriff

Comments

[no name] 2-Dec-19 9:58am

I did that as well "where Date between @fromdate AND @todate order by itemH.Date desc"
but someone told me there is another solution ?!

[no name] 2-Dec-19 10:01am

WHERE Date >= @fromdate and Date <= @todate
this one is working now it's from @0x01AA

User 11060979 · Accepted Answer · 2019-12-02T04:10:00

Solution 2

SQL

WHERE Date = @fromdate and Date =@todate

should be more

SQL

WHERE Date >= @fromdate and Date <= @todate

But the above is equivalent to Solution 1.

From my expirience in praxis one will find more this (note "<" instead of "<=":

SQL

WHERE Date >= @fromdate and Date < @todate

Posted 2-Dec-19 4:10am

User 11060979

Comments

Maciej Los 3-Dec-19 2:15am

5ed!

[no name] 3-Dec-19 4:29am

Thank you Maciej!