6 month gap doesn't produce result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

6 month gap doesn't produce result

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 02:05:13

hi,

iam having some strange problem. iam searching for records between from date and todate. this query works well except these conditions

a) if i give input say fromdate-2/2/2007 and todate-8/2/2007, the query goes for infinite execution time(pls note the difference between twodates is less than or equal to 6 months)

b)whereas if i give fromdate-2/2/2007 and todate-10/2/2007, the query works fine.(more than 6 months interval) seems strange for me.

pls if anyone came across these kind of errors and solved pls help me.
Thanks in advance

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 02:14:58

MY QUERY IS AS FOLLOWS

SELECT TOP 25 CouponID = C.iCouponID,
CouponListID = CL.iCouponListID,
CompanyName = CP.vCompanyName,
CompanyProfileID = CP.iProfileID,
CouponText = C.vCouponText,
CouponDesc = C.vDescription,
CouponCode = CL.vCouponCode,
CouponCodes = SUBSTRING(CL.vCouponCode,CHARINDEX('-',CL.vCouponCode) + 1,LEN(CL.vCouponCode)),
TransferedDate = CL.dTransferedDate,
RedeemedDate = (CASE WHEN CL.dUsedRedeemedDate IS NOT NULL THEN CONVERT(VARCHAR(15),CL.dUsedRedeemedDate,101) ELSE '-' END),
CouponValue = (SELECT CouponValue FROM CouponCount WHERE iCouponID = TP.iCouponID),
ParticipantName = MP.vFirstName+' '+MP.vLastName,
AccountNo = MP.vAccountNo,
UsedFor = CU.vUsed,
UsedFors = (CASE WHEN OFS.iCouponListID IS NULL THEN '0' ELSE CU.vUsed END),
RedeemedCompany = (CASE WHEN CL.vUsedRedeemedAt IS NOT NULL THEN CL.vUsedRedeemedAt ELSE '-' END),
TotalPrice = (CASE WHEN (SELECT top 1 vTotalPrice FROM OrderFormStorage WHERE iCouponListID = CL.iCouponListID) IS NULL THEN '-' ELSE 'US ' + (SELECT top 1 vTotalPrice FROM OrderFormStorage WHERE iCouponListID = CL.iCouponListID) END) ,

ProfileID = MP.iProfileID,
ParticipantID = MP.iProfileID,
Status = CL.iContStatusID FROM

(SELECT iCouponID,vCouponText,vDescription FROM Coupons) C

INNER JOIN (SELECT iCouponID,iCouponListID,iCreatedBy,iContStatusID,vUsedFor,vCouponCode,dTransferedDate,dUsedRedeemedDate,vUsedRedeemedAt,vDealerNo,iUpdatedBy FROM CouponLists) CL
ON C.iCouponID = CL.iCouponID

INNER JOIN (SELECT iCouponListID,iTranPartID,dTranDate FROM Trans2PartSub) TPS
ON CL.iCouponListID = TPS.iCouponListID

INNER JOIN (SELECT iCouponID,iTranPartID,iPartID FROM Trans2Part) TP
ON TPS.iTranPartID = TP.iTranPartID

INNER JOIN (SELECT iProfileID,vFirstName,vLastName,vAccountNo FROM MemberProfiles) MP
ON TP.iPartID = MP.iProfileID

INNER JOIN (SELECT iProfileID,vCompanyName FROM CompanyProfile) CP
ON CP.iProfileID = CL.iCreatedBy

INNER JOIN (SELECT iCoupUsedID,vUsed FROM CouponUsed) CU
ON CL.vUsedFor = CU.iCoupUsedID

LEFT OUTER JOIN (SELECT iOrderFormStorageID,iCouponListID FROM OrderFormStorage) OFS
ON CL.iCouponListID = OFS.iCouponListID

WHERE CL.dUsedRedeemedDate IS NOT NULL AND CL.iCreatedBy = 2726 AND TPS.dTranDate IS NULL AND (CL.dUsedRedeemedDate BETWEEN CAST('12/11/2006' AS DATETIME) AND CAST('2/2/2008' AS DATETIME) + 1) ORDER BY CouponText DESC

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 02:37:39

hi iam back.
i would like to slightly change my question.

execution time increases for condition a. say more than 2 min
while execution occurs within 5 seconds for condition b. where iam going wrong. please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 03:48:31

Have you had a look at execution plans for these two cases to get an idea of what is happening?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 04:16:23

Drop your 3 correlated subqueries

SELECT TOP 25	C.iCouponID AS CouponID,
		CL.iCouponListID AS CouponListID,
		CompanyName AS CP.vCompanyName,
		CP.iProfileID AS CompanyProfileID,
		C.vCouponText AS CouponText,
		C.vDescription AS CouponDesc,
		CL.vCouponCode AS CouponDesc,
		SUBSTRING(CL.vCouponCode, CHARINDEX('-',CL.vCouponCode) + 1, LEN(CL.vCouponCode)) AS CouponCodes,
		CL.dTransferedDate AS TransferedDate,
		COALESCE(CONVERT(VARCHAR(15), CL.dUsedRedeemedDate, 101), '-') AS TransferedDate,
		CC.CouponValue AS CouponValue,
		MP.vFirstName + ' ' + MP.vLastName AS ParticipantName,
		MP.vAccountNo AS AccountNo,
		CU.vUsed AS UsedFor,
		CASE
			WHEN OFS.iCouponListID IS NULL THEN '0'
			ELSE CU.vUsed
		END AS UsedFors,
		COALESCE(CL.vUsedRedeemedAt, '-') AS RedeemedCompany,
		COALESCE('US' + OFS.vTotalPrice, '-') AS TotalPrice,
		MP.iProfileID AS ProfileID,
		MP.iProfileID AS ParticipantID,
		CL.iContStatusID AS Status
FROM		Coupons AS C
INNER JOIN	CouponLists AS CL ON CL.iCouponID = C.iCouponID
INNER JOIN	Trans2PartSub AS TPS ON TPS.iCouponListID = CL.iCouponListID
INNER JOIN	Trans2Part AS TP ON TP.iTranPartID = TPS.iTranPartID
INNER JOIN	MemberProfiles AS MP ON MP.iProfileID = TP.iPartID
INNER JOIN	CompanyProfile AS CP ON CP.iProfileID = CL.iCreatedBy
INNER JOIN	CouponUsed AS CU ON CU.iCoupUsedID = CL.vUsedFor
LEFT JOIN	(
			SELECT		iCouponListID,
					MAX(vTotalPrice) AS vTotalPrice
			FROM		OrderFormStorage
			GROUP BY	iCouponListID
		) AS OFS ON OFS.iCouponListID = CL.iCouponListID
LEFT JOIN	CouponCount AS CC ON CC.iCouponID = TP.iCouponID
WHERE		CL.dUsedRedeemedDate IS NOT NULL
		AND CL.iCreatedBy = 2726
		AND TPS.dTranDate IS NULL
		AND CL.dUsedRedeemedDate >= '20061112'
		AND CL.dUsedRedeemedDate < '20080203'
ORDER BY	CouponText DESC

E 12°55'05.25"
N 56°04'39.16"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 04:20:27

Thanks peso and vishak . let me try ur thought and come up with result and tell u back about the update

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 04:30:08

Sorry sir, iam not able to get the result. it seems strange

if i give dates between 1/1/2001 and 1/1/2008. the result comes in a flash

whereas if i give dates between 12/11/2007 and 2/2/2008 . execution time takes more than 1 min.first it took more than 2 min thanks to peso right now it comes more than 1 min.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 04:49:15

Are you sure dUsedRedeemedDate is a DATETIME or SMALLDATETIME column?
It seems to me that dUsedRedeemedDate is a VARCHAR column.

Why I think that?

Because when you use "1/1/2001" and "1/1/2008" there are only 8 dates fulfilling your request and they are
"1/1/2001", "1/1/2002", "1/1/2003", "1/1/2004", "1/1/2005", "1/1/2006", "1/1/2007" and "1/1/2008".

When you use "12/11/2007" and "2/2/2008" there are a lot more dates fulfilling your request.

E 12°55'05.25"
N 56°04'39.16"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 04:52:24

Sir,

its a datetime column not varchar.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 04:53:52

sir my whole procedure

exec pr_redemptiondetails 25,60,'CouponText','DESC',2726,'12/11/2006','2/2/2008','',0,''

ALTER PROCEDURE pr_RedemptionDetails
(
@PAGESIZE INT,
@CURRENTPAGE INT,
@SORTEXPRESSION NVARCHAR(100) ='',
@SORTDIRECTION NVARCHAR(100) = '',
@iProfileID INT,
@dFromDate VARCHAR(30) = '',
@dToDate VARCHAR(30) = '',
@iCouponID VARCHAR(30) = '',
@iOnlineStatus INT = '',
@vSearchCond VARCHAR(100) = ''
)
AS
BEGIN

IF(EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '%#TEMPTABLE%'))
BEGIN
DROP TABLE #TEMPTABLE
END
/* DECLARE TABLE VARIABLE */
DECLARE @TEMPTABLE TABLE
(
ROWID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
CouponID NVARCHAR(100),
CouponListID NVARCHAR(1000),
CompanyName VARCHAR(500),
CompanyProfileID INT,
CouponText NVARCHAR(300),
CouponDesc NVARCHAR(3500),
CouponCode NVARCHAR(500),
CouponCodes NVARCHAR(100),
TransferedDate NVARCHAR(100),
RedeemedDate NVARCHAR(100),
CouponValue NVARCHAR(500),
ParticipantName NVARCHAR(500),
AccountNo NVARCHAR(50),
UsedFor NVARCHAR(60),
UsedFors NVARCHAR(100),
RedeemedCompany NVARCHAR(250),
TotalPrice NVARCHAR(100),
ProfileID NVARCHAR(20),
ParticipantID INT,
Status NVARCHAR(20)
)

DECLARE @TEMP1 TABLE
(
COUNTID INT
)

/* DECLARE VARIABLES */
DECLARE @DYNQUERY VARCHAR(7000)
DECLARE @DYNQUERY1 VARCHAR(7000)
DECLARE @QUERY VARCHAR(7000)
DECLARE @SortExp NVARCHAR(1000)
DECLARE @RECORDCOUNT INT

SET @RECORDCOUNT = @PAGESIZE * @CURRENTPAGE

SET @DYNQUERY = 'SELECT TOP ' + CONVERT(VARCHAR(20),@RECORDCOUNT)
SET @DYNQUERY1 = 'SELECT COUNTID = COUNT(C.iCouponID) '
SET @DYNQUERY = @DYNQUERY + ' CouponID = C.iCouponID,
CouponListID = CL.iCouponListID,
CompanyName = CP.vCompanyName,
CompanyProfileID = CP.iProfileID,
CouponText = C.vCouponText,
CouponDesc = C.vDescription,
CouponCode = CL.vCouponCode,
CouponCodes = SUBSTRING(CL.vCouponCode,CHARINDEX(''-'',CL.vCouponCode) + 1,LEN(CL.vCouponCode)),
TransferedDate = CL.dTransferedDate,
RedeemedDate = COALESCE(CONVERT(VARCHAR(15), CL.dUsedRedeemedDate, 101), ''-''),
CouponValue = CC.CouponValue,
ParticipantName = MP.vFirstName+'' ''+MP.vLastName,
AccountNo = MP.vAccountNo,
UsedFor = CU.vUsed,
UsedFors = (CASE WHEN OFS.iCouponListID IS NULL THEN ''0'' ELSE CU.vUsed END),
RedeemedCompany = COALESCE(CL.vUsedRedeemedAt, ''-''),
TotalPrice = COALESCE(''US'' + OFS.vTotalPrice, ''-''),

ProfileID = MP.iProfileID,
ParticipantID = MP.iProfileID,
Status = CL.iContStatusID'

SET @QUERY = ' FROM

(SELECT iCouponID,vCouponText,vDescription FROM Coupons) C

INNER JOIN (SELECT iCouponID,iCouponListID,iCreatedBy,iContStatusID,vUsedFor,vCouponCode,dTransferedDate,dUsedRedeemedDate,vUsedRedeemedAt,vDealerNo,iUpdatedBy FROM CouponLists) CL
ON C.iCouponID = CL.iCouponID

INNER JOIN (SELECT iCouponListID,iTranPartID,dTranDate FROM Trans2PartSub) TPS
ON CL.iCouponListID = TPS.iCouponListID

INNER JOIN (SELECT iCouponID,iTranPartID,iPartID FROM Trans2Part) TP
ON TPS.iTranPartID = TP.iTranPartID

INNER JOIN (SELECT iProfileID,vFirstName,vLastName,vAccountNo FROM MemberProfiles) MP
ON TP.iPartID = MP.iProfileID

INNER JOIN (SELECT iProfileID,vCompanyName FROM CompanyProfile) CP
ON CP.iProfileID = CL.iCreatedBy

INNER JOIN (SELECT iCoupUsedID,vUsed FROM CouponUsed) CU
ON CL.vUsedFor = CU.iCoupUsedID

INNER JOIN (SELECT iCouponid,CouponValue FROM CouponCount) CC
ON CC.iCouponID = TP.iCouponID

LEFT OUTER JOIN (SELECT iOrderFormStorageID,iCouponListID,vTotalPrice FROM OrderFormStorage) OFS
ON CL.iCouponListID = OFS.iCouponListID

WHERE CL.dUsedRedeemedDate IS NOT NULL AND CL.iCreatedBy = ' + CAST(@iProfileID AS VARCHAR(15)) + ' AND TPS.dTranDate IS NULL AND '

/* SEARCH BY DATE */
IF (@iCouponID = '' AND @vSearchCond = '' AND @dFromDate <> '' )
BEGIN
SET @QUERY = @QUERY + '(CL.dUsedRedeemedDate BETWEEN CAST(''' + @dFromDate + ''' AS DATETIME) AND CAST(''' + @dToDate
+ ''' AS DATETIME) + 1) '
END
/* SEARCH BY AWARD ID */
ELSE IF (@iCouponID <> '' AND @vSearchCond = '' AND @dFromDate = '' )
BEGIN
SET @QUERY = @QUERY + ' SUBSTRING(cl.vCouponCode,CHARINDEX(''-'',CL.vCouponCode) + 1,LEN(CL.vCouponCode)) =
(CASE WHEN LEN(''' + @iCouponID + ''') = 8 THEN ''' + @iCouponID + ''' ELSE REPLICATE(''0'',8-LEN('''+ @iCouponID + ''')) + ''' + @iCouponID + ''' END) '
END
--SEARCH ALL BY DESCRIPTION AND TEXT
ELSE IF(@iCouponID = '' AND @vSearchCond <> '' AND @dFromDate = '' )
BEGIN
SET @QUERY = @QUERY + '(C.vDescription LIKE ''%' + @vSearchCond + '%'' OR C.vCouponText LIKE ''%' + @vSearchCond + '%'' OR CP.vCompanyName LIKE ''%' + @vSearchCond + '%'' OR (MP.vFirstName LIKE ''%' + @vSearchCond + '%'' OR MP.vLastName LIKE ''%' +

@vSearchCond + '%'') ) '
END

IF(@IONLINESTATUS = 1)
BEGIN
SET @QUERY = @QUERY + 'AND CL.vDealerNo IS NULL AND CL.vUsedFor = 20 '
END

SET @DYNQUERY1 = @DYNQUERY1 + @QUERY

IF(@SortExpression IS NOT NULL AND @SortExpression <> '')
IF (@SortExpression = 'CouponCodes')
BEGIN
SET @SORTEXP = ' ORDER BY SUBSTRING(vCouponCode,CHARINDEX(''-'',vCouponCode) + 1,LEN(vCouponCode))' + ' ' + @SortDirection
END
ELSE
BEGIN
SET @SORTEXP = ' ORDER BY ' + @SortExpression + ' ' + @SortDirection
END
ELSE
SET @SORTEXP = ' ORDER BY CompanyName '

SET @QUERY = @QUERY + @SORTEXP

SET @DYNQUERY = @DYNQUERY + @QUERY

INSERT INTO @TEMPTABLE(CouponID,CouponListID,CompanyName,CompanyProfileID,CouponText,CouponDesc,CouponCode,CouponCodes,
TransferedDate,RedeemedDate,CouponValue,ParticipantName,AccountNo,UsedFor,UsedFors,RedeemedCompany,
TotalPrice,ProfileID,ParticipantID,Status)
EXEC(@DYNQUERY)
PRINT @DYNQUERY
SELECT * FROM @TEMPTABLE WHERE ROWID BETWEEN (CASE WHEN @CURRENTPAGE = 1 THEN @CURRENTPAGE WHEN @CURRENTPAGE > 1 THEN (@RECORDCOUNT- @PAGESIZE)+ 1 END) AND @RECORDCOUNT
--PRINT @DYNQUERY1
INSERT INTO @TEMP1 (COUNTID)
EXEC(@DYNQUERY1)
SELECT * FROM @TEMP1

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 05:00:09

ALWAYS USE PROPER DATATYPE FOR STORING DATES!
And if you don't, ALWAYS USE ISO FORMAT (YYYYMMDD) WHEN COMPARING DATES!

You need to learn string comparison...

And you also seems to do some pagination here. There are better ways to do this than your code.

SET @QUERY = @QUERY + '(CONVERT(CHAR(8), CL.dUsedRedeemedDate , 112) BETWEEN CONVERT(CHAR(8), ''' + @dFromDate + ''', 112) AND CONVERT(CHAR(8), ''' + @dToDate + ''', 112)) '

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 05:02:25

And this is what kills your query regarding speed...

E 12°55'05.25"
N 56°04'39.16"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 05:07:03

Sir, iam not efficient in sql and i will try to be good in sql on ur advice. but iam still stuck with the prob. the pagination works fine for large date interval but gets stuck up for small date interval(may be lesse days). any other way to overcome the prob. i cant find exactly where it occurs and i tried all ur remedies.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 05:13:19

SIR, IF I CONVERT TO CHAR I DONT GET ANY RESULT FOR ANY CONDITIONS. THANKS FOR SPENDING UR VALUABLE TIME AND PATIENCLY ANSWERING MY QUESTIONS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 05:22:10

Try this

SET @QUERY = @QUERY + '(CAST(CL.dUsedRedeemedDate AS DATETIME) BETWEEN CONVERT(DATETIME, ''' + @dFromDate + ''', 101) AND CONVERT(DATETIME, ''' + @dToDate + ''', 101)) '

E 12°55'05.25"
N 56°04'39.16"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 05:32:17

SORRY SIR, IAM NOT ABLE TO PRODUCE THE OUTPUT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 05:32:36

Here is an example of how you compare date (as string) and how I compare dates (as dates)

SELECT	COUNT(*) AS [All dates between 20071211 and 20080202],
	SUM(CASE WHEN CONVERT(VARCHAR(10), DATE, 101) BETWEEN '12/11/2007' AND '2/2/2008' THEN 1 ELSE 0 END) AS dineshrajan_it,
	SUM(CASE WHEN CONVERT(VARCHAR(8), DATE, 112) BETWEEN '20071112' AND '20080202' THEN 1 ELSE 0 END) AS Peso
FROM	F_TABLE_DATE('20071211', '20080202')

SELECT	CONVERT(VARCHAR(10), DATE, 101) AS theDate,
	'12/11/2007' AS StartDate,
	'2/2/2008' AS EndDate,
	CASE WHEN CONVERT(VARCHAR(10), DATE, 101) BETWEEN '12/11/2007' AND '2/2/2008' THEN 1 ELSE 0 END AS dineshrajan_it,
	CASE WHEN CONVERT(VARCHAR(8), DATE, 112) BETWEEN '20071112' AND '20080202' THEN 1 ELSE 0 END AS Peso
FROM	F_TABLE_DATE('20071211', '20080202')

Output is


All dates between 20071211 and 20080202	dineshrajan_it	Peso
54					21		54

theDate		StartDate	EndDate		dineshrajan_it	Peso
12/11/2007	12/11/2007	2/2/2008	1		1
12/12/2007	12/11/2007	2/2/2008	1		1
12/13/2007	12/11/2007	2/2/2008	1		1
12/14/2007	12/11/2007	2/2/2008	1		1
12/15/2007	12/11/2007	2/2/2008	1		1
12/16/2007	12/11/2007	2/2/2008	1		1
12/17/2007	12/11/2007	2/2/2008	1		1
12/18/2007	12/11/2007	2/2/2008	1		1
12/19/2007	12/11/2007	2/2/2008	1		1
12/20/2007	12/11/2007	2/2/2008	1		1
12/21/2007	12/11/2007	2/2/2008	1		1
12/22/2007	12/11/2007	2/2/2008	1		1
12/23/2007	12/11/2007	2/2/2008	1		1
12/24/2007	12/11/2007	2/2/2008	1		1
12/25/2007	12/11/2007	2/2/2008	1		1
12/26/2007	12/11/2007	2/2/2008	1		1
12/27/2007	12/11/2007	2/2/2008	1		1
12/28/2007	12/11/2007	2/2/2008	1		1
12/29/2007	12/11/2007	2/2/2008	1		1
12/30/2007	12/11/2007	2/2/2008	1		1
12/31/2007	12/11/2007	2/2/2008	1		1
01/01/2008	12/11/2007	2/2/2008	0		1
01/02/2008	12/11/2007	2/2/2008	0		1
01/03/2008	12/11/2007	2/2/2008	0		1
01/04/2008	12/11/2007	2/2/2008	0		1
01/05/2008	12/11/2007	2/2/2008	0		1
01/06/2008	12/11/2007	2/2/2008	0		1
01/07/2008	12/11/2007	2/2/2008	0		1
01/08/2008	12/11/2007	2/2/2008	0		1
01/09/2008	12/11/2007	2/2/2008	0		1
01/10/2008	12/11/2007	2/2/2008	0		1
01/11/2008	12/11/2007	2/2/2008	0		1
01/12/2008	12/11/2007	2/2/2008	0		1
01/13/2008	12/11/2007	2/2/2008	0		1
01/14/2008	12/11/2007	2/2/2008	0		1
01/15/2008	12/11/2007	2/2/2008	0		1
01/16/2008	12/11/2007	2/2/2008	0		1
01/17/2008	12/11/2007	2/2/2008	0		1
01/18/2008	12/11/2007	2/2/2008	0		1
01/19/2008	12/11/2007	2/2/2008	0		1
01/20/2008	12/11/2007	2/2/2008	0		1
01/21/2008	12/11/2007	2/2/2008	0		1
01/22/2008	12/11/2007	2/2/2008	0		1
01/23/2008	12/11/2007	2/2/2008	0		1
01/24/2008	12/11/2007	2/2/2008	0		1
01/25/2008	12/11/2007	2/2/2008	0		1
01/26/2008	12/11/2007	2/2/2008	0		1
01/27/2008	12/11/2007	2/2/2008	0		1
01/28/2008	12/11/2007	2/2/2008	0		1
01/29/2008	12/11/2007	2/2/2008	0		1
01/30/2008	12/11/2007	2/2/2008	0		1
01/31/2008	12/11/2007	2/2/2008	0		1
02/01/2008	12/11/2007	2/2/2008	0		1
02/02/2008	12/11/2007	2/2/2008	0		1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 05:38:32

Try this

SET @QUERY = @QUERY + '(CAST(CL.dUsedRedeemedDate AS DATETIME) BETWEEN ''' + CONVERT(VARCHAR(8), CAST(@FromDate AS DATETIME), 112) + ''' AND ''' + CONVERT(CHAR(8), CAST(@ToDate AS DATETIME), 112) + ''')'

E 12°55'05.25"
N 56°04'39.16"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-02-18 : 05:56:07

sir, u provide a good comparision between mine and urs. i will follow ur set of coding for date comparisons. but iam struggling yet

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 07:53:57

quote:
Originally posted by dineshrajan_it

sir, u provide a good comparision between mine and urs. i will follow ur set of coding for date comparisons. but iam struggling yet

Apply the above logic for all your date comparison in your query
Also refer
www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources