Click here to Skip to main content
14,237,873 members
Rate this:
Please Sign up or sign in to vote.
See more:
i need to display records in data grid based on the from and to dates....but i have only records createddate column in my table in the data base

What I have tried:

ALTER PROCEDURE [dbo].[SP_PURCHASE]
	(

		@MODE INT,
		
       @CRDATE DATETIME = NULL,
	   @FDATE DATETIME =NULL,
		@TODATE DATETIME=NULL

		
	)
 AS
 DECLARE @RESULT INT
SET @RESULT=0

ELSE IF @MODE = 6
 BEGIN
 select * from TR_PURCHASE  WHERE 
			CRDATE BETWEEN ' + ''''+	CONVERT(VARCHAR(3),DATEPART(MM,@FDATE))+'/'+
									CONVERT(VARCHAR(3),DATEPART(DD,@FDATE))+'/'+
									CONVERT(VARCHAR(4),DATEPART(YYYY,@FDATE)) + ''''+
			' AND ' +  ''''+CONVERT(VARCHAR(3),DATEPART(MM,@TODATE))+'/'+
							CONVERT(VARCHAR(3),DATEPART(DD,@TODATE))+'/'+
							CONVERT(VARCHAR(4),DATEPART(YYYY,@TODATE))+''' AND BCODE=+ '''' +CONVERT(VARCHAR(15),@BCODE)+'''';
 end
  IF @@ROWCOUNT > 0
     SET @RESULT = 1
Posted
Updated 27-Apr-16 0:13am
v4
Comments
ZurdoDev 25-Apr-16 8:32am
   
So, what is your question?
CHill60 25-Apr-16 8:35am
   
Get rid of all those CONVERTs --- CRDATE should be a Date or DateTime column - if it is a varchar or char then change it and just use
select * from TR_PURCHASE WHERE CRDATE BETWEEN @FDATE and @TODATE
You probably don't need the CONVERT on @BCODE either - just use AND BCODE = @BCODE

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Why not just use
SELECT * FROM Tr_Purcahse WHERE CreatedDate BETWEEN @FDate AND @TODate AND BCODE = @BCODE

The example you show will either return all records where BCODE matches, or none at all depending on the value you pass to the procedure in @CRDATE, not the value in the DB.
Unless your CreatedDate column is an NVARCHAR - in which case change your DB and make it a DATETIME before it starts giving you real problems.
   
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100