Click here to Skip to main content
15,891,938 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Dear All

This is my stored procedure: but getting error: Conversion failed when converting datetime from character string.

please give me solution for the same

-- =============================================            
-- Author:  <Author,,Name>            
-- Create date: <Create Date,,>            
-- Description: <Description,,>            
-- =============================================            
ALTER PROCEDURE Usp_GetSlabByMemberIDANDExpectedDate
@MemberID BIGINT,    
@ExpectedDate DATETIME        
AS          
BEGIN          
 DECLARE @cnt INT          
 SET @cnt = (SELECT COUNT(*) FROM tbl_MemberPackage  WHERE ExpectedDate <= GETDATE()  AND MemberID = @MemberID AND IsWorkDone IS NULL )          
        
 IF @cnt = 0          
 BEGIN          
   SELECT  --top(1)        
     t1.MemberFirstName + ' ' + t1.MemberLastName AS MemberName,        
     t2.MemberID,        
     t2.ExpectedDate,        
     t3.WorkName        
   FROM tbl_MemberPackage  t2 INNER JOIN tbl_Membermaster t1        
   ON t1.MemberID = t2.MemberID        
      INNER JOIN tbl_TypeOfWork t3        
   ON t3.WorkID = t2.WorkID            
WHERE t2.MemberID = @MemberID         
   AND t2.IsWorkDone IS NULL          
 AND t2.ExpectedDate = '@ExpectedDate'  
 END          
 ELSE        
      SELECT t1.MemberFirstName + ' ' + t1.MemberLastName AS MemberName,        
             t2.MemberID,        
             t2.ExpectedDate,        
			 t3.WorkName        
	FROM tbl_MemberPackage  t2 INNER JOIN tbl_Membermaster t1        
    ON t1.MemberID = t2.MemberID         
			INNER JOIN tbl_TypeOfWork t3        
	ON t3.WorkID = t2.WorkID    
	WHERE              
		t2.MemberID = @MemberID         
		AND t2.IsWorkDone IS NULL     
		AND t2.ExpectedDate = '@ExpectedDate'      
	END 
Posted
Comments
LaxmikantYadav 2-May-11 5:07am    
Convert your ExpectedDate i.e CONVERT(VARCHAR(30),@ExpectedDate,113)
into some variable and use that variable all over in the script insted of @ExpectedDate

1 solution

Without seeing the data you are feeding the SP, it is difficult to be sure, but the most likely cause is that SQL always expects dates in ISO 8601 format: "yyyy-MM-dd" or "yyyy-MM-dd hh:mm:ss".
If you feed it a string with a different date format, there will be an error.

The best solution if you can is to feed it a DateTime rather than a string - i.e. do the conversion outside the SP.
 
Share this answer
 

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