如何在SQL Server 2008中将DateTime作为varchar传递？ [英] How to pass DateTime as varchar in SQL Server 2008?

问题描述

我正在尝试将DateTime作为检查条件FromDate和ToDate的输入参数传递，但我收到的错误如



转换失败当从字符串转换日期和/或时间时。



我的存储过程是这样的

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

@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

那么如何修复这个问题？

推荐答案

试试这样 -

set @sql = @sql +'And a.regdate> ='+ convert（varchar，@ FromDate）

try like this -
set @sql = @sql + 'And a.regdate >= ' + convert(varchar,@FromDate)

您好，



您的数据类型是.regdate是varchar？



如果是，那么你可以在你的情况下使用这个



convert（ date，a.regdate）> = convert（date，@ FromDate）





希望这可以解决您的问题。



问候

Mubin

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

这篇关于如何在SQL Server 2008中将DateTime作为varchar传递？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！