Click here to Skip to main content
15,889,931 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to pass DateTime as a Input parameter for check condition FromDate and ToDate, But I'm getting error like

"Conversion failed when converting date and/or time from character string."

And My stored procedure is like this


SQL
@MemberType int,
@Period int,
@UnitNo int,
@BranchUserId varchar(100),
@FromDate datetime,
@ToDate datetime

AS

BEGIN

DECLARE @sql AS NVarchar(4000)

    SET @sql = 'select a.userid as [UserID],b.fullname as [Full Name],CONVERT(date,a.regdate,112) as [Joined Date],a.period as [Month Period],a.unitno as [UnitNo],b.address as [Address] from users a join profiles b on a.accountid=b.accountid where (1=1)'
    
    if @FromDate is not null
    set @sql = @sql + 'And a.regdate >= ' + @FromDate + ''
    
    if @ToDate is not null
    set @sql = @sql + 'And a.regdate <= ' + @ToDate + ''
    
    if @MemberType is not null
    set @sql = @sql + ' And a.membertype = ' + @MemberType
    
    if @Period is not null
    set @sql = @sql + ' And a.period = ' + @Period
        
    if @UnitNo is not null
    set @sql = @sql + ' And a.unitno = ' + @UnitNo
    
    if @BranchUserId is not null
    set @sql = @sql + ' And a.branchuserid = ' + @BranchUserId + ''
 
EXEC(@sql)

END


So How can I fix this problem?
Posted

try like this -
set @sql = @sql + 'And a.regdate >= ' + convert(varchar,@FromDate)
 
Share this answer
 
v2
Comments
Aboobakkar Siddeq D U 4-Oct-13 8:53am    
No I don't think so, because i've tried this and i'm getting in this

select a.userid as [UserID],b.fullname as [Full Name],CONVERT(date,a.regdate,112) as [Joined Date],a.period as
[Month Period],a.unitno as [UnitNo],b.address as [Address] from users a join profiles b on a.accountid=b.accountid
where (1=1) And a.regdate >= '08/01/2013' And a.regdate <= '08/25/2013' And a.membertype =3
Madhu Nair 4-Oct-13 8:55am    
change the format of the date to yyyy-MM-dd and try
Aboobakkar Siddeq D U 5-Oct-13 0:33am    
I tried many times, where should I change date format? In frontend or backend? How to change format of DateTime?
Hi,

Is your data type of a.regdate is varchar?

if yes then you can use this in your where condition

convert(date,a.regdate) >=convert(date,@FromDate)


Hope this will solve your problem.

Regards
Mubin
 
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