Conversion failed when converting datetime from character string RSS

• • Reply
  

  Phinehas

  Member

  361 Points

  238 Posts

  Conversion failed when converting datetime from character string

  Apr 26, 2012 03:33 AM|LINK

  Hi,

  I have a store procedure that will receive some datetime parameter. However, I find that it cannot get the correct datetime and returns me the error.

  The store procedure is the following:

  ALTER PROCEDURE [dbo].[sp_tender_rpt_search_list]
  	(
  		@user_id VARCHAR(15),
  		@rec_date_f DATETIME = NULL,
  		@rec_date_t DATETIME = NULL
  	)
  AS
  	SET NOCOUNT ON;
  	SET ARITHABORT ON;
  
  	DECLARE @sql VARCHAR(MAX);
  
  	SET @sql = 'SELECT * FROM testTable WHERE record_date >= @rec_date_f AND record_date <= rec_date_t'
  	EXEC (@sql)
  
  RETURN(0)

  When I input:

  EXEC @return_value = [dbo].[sp_tender_rpt_search_list]
  @user_id = N'tester',
  @rec_date_f = '2012-01-01',
  @rec_date_t = '2012-12-31'

  SELECT 'Return Value' = @return_value

  
  It shows me error: Conversion failed when converting datetime from character string

  How can I solve it?

  Phinehas.
• • Reply
  

  Rab Nawaz Kh...

  Participant

  955 Points

  209 Posts

  Re: Conversion failed when converting datetime from character string

  Apr 26, 2012 03:47 AM|LINK

  your date time format is not correct, provde the parameters in the following formate to the store procedure

  EXEC @return_value = [dbo].[sp_tender_rpt_search_list]
  @user_id = N'tester',
  @rec_date_f = CONVERT(varchar(100), '2012-01-01', 103),
  @rec_date_t = CONVERT(varchar(100), '2012-12-31', 103)

  Hope it will work for you

  Please mark as answer if this post answered or solved your problem.
• • Reply
  

  shivalthakur

  Participant

  1839 Points

  532 Posts

  Re: Conversion failed when converting datetime from character string

  Apr 26, 2012 03:54 AM|LINK

  hi try this

  EXEC @return_value = [dbo].[sp_tender_rpt_search_list]
  @user_id = N'tester',
  @rec_date_f = Convert(date,'2012-01-01',102),
  @rec_date_t = Convert(date,'2012-12-31,102)'
  
  SELECT 'Return Value' = @return_value

  hope help you

  if(Hard_Work && Luck)
  Response.Write("Success");
  
  
  Best Of Luck
  Shival Thakur
• • Reply
  

  kelviyean

  Member

  228 Points

  54 Posts

  Re: Conversion failed when converting datetime from character string

  Apr 26, 2012 05:08 AM|LINK

  Hello,

  Use the below line.

  SET @sql = 'SELECT * FROM testTable WHERE record_date >= ' + @rec_date_f + ' AND record_date <=' + rec_date_t

• • Reply
  

  Phinehas

  Member

  361 Points

  238 Posts

  Re: Conversion failed when converting datetime from character string

  Apr 26, 2012 05:22 AM|LINK

  I have tried but fail.

  It returns error message: Incorrect syntax near the keyword 'CONVERT'.

  Phinehas.
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Conversion failed when converting datetime from character string

  Apr 26, 2012 05:57 AM|LINK

  ALTER PROCEDURE [dbo].[sp_tender_rpt_search_list]
  (
          @user_id VARCHAR(15),
          @rec_date_f DATETIME = NULL,
          @rec_date_t DATETIME = NULL
  )
  AS
  SET NOCOUNT ON;
  SET ARITHABORT ON;
  
  DECLARE @sql VARCHAR(MAX);
  DECLARE @param VARCHAR(MAX);
  
  --use parameterized dynamic sql
  SET @param='@rec_date_f DATETIME,@rec_date_t DATETIME'
  
  SET @sql = 'SELECT * FROM testTable WHERE record_date >= @rec_date_f AND record_date <= rec_date_t'
  
  --pass parameter to dynamic query
  --this will omitt the sql injection problem
  
  exec sp_executesql @sql,@param,@rec_date_f,@rec_date_t

  Yogesh Bhadauriya
  .Net All About
  
  • Marked as answer by Phinehas on Apr 26, 2012 08:37 AM