[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Date Range problem

Posted on 2008-01-30
3
Medium Priority
?
162 Views
Last Modified: 2010-03-20
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

0
Comment
Question by:tia_kamakshi
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 20776397
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
 
LVL 26

Expert Comment

by:ee_rlee
ID: 20786155
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 20786520
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

607 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