Click here to Skip to main content
15,922,427 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi every one; I have a stored proc and I need to pass datetime variables (@FromDate and @ToDate) to it.
The conversion failed when converting date and/or time from character string.
I tried to use convert function but it didn't work.
Can any one help me?

SQL
(
@page int = 1,
@pagesize int = 10,
@FromDate datetime,
@ToDate datetime
)
as
declare @sql nvarchar(max);
declare @sql2 nvarchar(max);
declare @countout int=0;

set @sql='';
set @sql2='';
declare	@lbound int, @ubound int
		
set @lbound = 1;
set @ubound = 100000;
set @sql='select @countout=count(*) from Trans where Trans.Date between '+ @FromDate +' and '+ @ToDate

Declare @sqlParam nvarchar(100)
set @sqlParam = ' @countOut int output ';

exec sp_executeSQL @sql,@sqlParam,@countOut output;
if @countOut=0
   return 0;
Posted
Updated 18-Jun-12 8:13am
v2
Comments
ZurdoDev 18-Jun-12 14:21pm    
Do a SQL trace and see what is being passes exactly.
Sandeep Mewara 18-Jun-12 14:43pm    
Issue is the value and the way you have passed the datetime fields.

SP is just fine and I doubt if this is relevant here.

How are you passing the values to this SP?
db7uk 18-Jun-12 16:45pm    
When you say you have tried the convert. What happened? I normally do this if working with dynamic sql:
select @countout=count(*) from Trans where Trans.Date between convert(datetime, '+ @FromDate +', 103) and convert(datetime, '+ @ToDate + ', 103)

use convert command for this and write like this it will work

SQL
set @sql='select @countout=count(*) from Trans where Trans.Date between '+ convert(datetime, convert(char(10),@FromDate, 110)) +' and '+ convert(datetime, convert(char(10),@ToDate, 110))
 
Share this answer
 
Comments
khangaldi 19-Jun-12 3:23am    
i don't know why but this code didn't work , although it seem correct
how to convert string to time ?

Hi ,


please Convert to data Time and pass the parameters
See example below:

Dim strTime As String = "3:00 PM"

' Convert to datetime
Dim dtTime As DateTime = Convert.ToDateTime(strTime)

' Display in 24hr format

Response.Write(dtTime.ToString("HH:mm"))
 
Share this answer
 

Tanx Guys but this is how i solved it:

SQL
declare @sql nvarchar(max);
declare @sql2 nvarchar(max);
declare @countout int=0;
declare @Tempsql nvarchar(max);

set @sql='';
set @Tempsql='';
DECLARE	@lbound int,
		@ubound int
		
		
SET @lbound = 1;
SET @ubound = 100000;


set @sql='select @countout=count(*) from Trans tn '

if @FromDate is not null
begin 
	set @Tempsql= @Tempsql+' where  tn.Date >= ''' + convert(varchar, @FromDate,20)+'''';
end 
 if @ToDate is not  null 
begin
set @ToDate= DATEADD("day", 1, @ToDate)
set @Tempsql= @Tempsql+' and  tn.Date <= ''' + convert(varchar, @ToDate,20)+'''';
end

set @sql= @sql + @Tempsql;
Declare @sqlParam nvarchar(100)
SET @sqlParam = '@countOut int output';
EXEC sp_executeSQL @sql,@sqlParam,@countOut OUTPUT;
if (@countOut=0)
return 0;
EXEC dbo.Paging_Get_Bounds @countOut,@page , @pagesize, @lbound output , @ubound output ;
set @sql=' select '+CONVERT(varchar, @countOut)+' as countOut,*            from (
 select  (ROW_NUMBER() OVER (ORDER BY tn.Date DESC) ) as rownumber,*       
 from Trans tn
)as tbl
where rownumber between  '+CONVERT(varchar, @lbound)+' and '+CONVERT(varchar, @ubound);

EXEC sp_executeSQL @sql
 
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