Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

SQL
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

Why not just use
SQL
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.
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900