Date Range problem

Hi,

I am working on MSSQL 2000

I have problem with getting data from date range.

I have to find the total no of orders between fromdate and todate.

In my test applictaion I have given order for dated 30 Jan 2008 for quantity 2

When in stored proc when I pass the parameter @sfromdate and @sTodate

It does not include the last day of @todate

I mean if I pass the date 31 Jan 08 the it adds the quantity but If I pass 30 Jan 08, it does not add the qunatity


Please guide
exec rptCustomerOrderHistoryOverview '20070130' '20080130'
 
 
alter PROCEDURE rptCustomerOrderHistoryOverview
(
	@sFromDate varchar(10),
	@sToDate varchar(10)
	
)
AS
 
SELECT SUM(dbo.OrderLine.Quantity) AS Qty, OrderLine.ProductId,
                      dbo.Customer.Fname, dbo.Customer.Sname, dbo.Customer.Country, 
                      dbo.Customer.CompanyName, dbo.Customer.id AS CustomerId, PRODUCTDESCRIPTION.ShortDesc, PRODUCT.Np4uExternalPartNumber
		FROM
			dbo.OrderLine 
			INNER JOIN dbo.[Order] 
				ON dbo.OrderLine.OrderId = dbo.[Order].id 
			INNER JOIN  dbo.Customer 
				ON dbo.[Order].CustomerId = dbo.Customer.id
			INNER JOIN PRODUCT 
				ON PRODUCT.ID = OrderLine.ProductId
			INNER JOIN PRODUCTDESCRIPTION
				ON PRODUCTDESCRIPTION.PRODUCTID = PRODUCT.ID
		WHERE     
			[Order].dateCreated >= '+CHAR(39) +@sFromDate + CHAR(39) +' AND [Order].dateCreated <= '+CHAR(39) +@sToDate +CHAR(39)+'
			and [Order].isCancelled = 0	
		GROUP BY
		      OrderLine.ProductId,
                      dbo.Customer.Fname, dbo.Customer.Sname, dbo.Customer.Country, 
                      dbo.Customer.CompanyName, dbo.Customer.id,
		      PRODUCTDESCRIPTION.ShortDesc, PRODUCT.Np4uExternalPartNumber

Open in new window

tia_kamakshiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
your dates probably have time components as well.

So  it's not really Jan. 30, 2008,  but Jan. 30, 2008 15:37pm  or something like that
so to include that data you have to go up to the end of the day which is less than Jan. 31, 2008


Change the end point clause of your query to....

"AND [Order].dateCreated < dateadd(d,1, '+CHAR(39) +@sToDate +CHAR(39)+')"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ee_rleeCommented:
or try this

DATEADD(day,0,[Order].dateCreated) will remove the time part of your date/time field.
 WHERE DATEADD(day,0,[Order].dateCreated) Between '+CHAR(39)+@sFromDate + CHAR(39) +' AND '+CHAR(39) +@sToDate +CHAR(39)+'

Open in new window

0
sdstuberCommented:
applying DATEADD to the column requires a function evaluation on every row of data processed.  Applying it to the parameter is resolved once at sql parse time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.