以下是我写的sql存储过程,报错:
消息 242,级别 16,状态 3,第 1 行
从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。
ALTER PROCEDURE [dbo].[Customers_Search]
(
@CustomerName nvarchar(50),
@VIPNumber varchar(50),
@Worker nvarchar(50),
@Phone varchar(50),
@MPhone varchar(50),
@IDCard varchar(50),
@startDate varchar(50),
@endDate varchar(50)
)
AS
BEGIN
declare @sql varchar(2000),@where varchar(2000)
set @sql=' Select * From C_Customers '
set @where=' where 1=1 '
if(@CustomerName<>'')
set @where=@where+' And CustomerName like ''%'+@CustomerName+'%'''
if(@VIPNumber<>'')
set @where=@where+' And VIPNumber like ''%'+@VIPNumber+'%'''
if(@Worker<>'')
set @where=@where+' And Worker like ''%'+@Worker+'%'''
if(@Phone<>'')
set @where=@where+' And Phone like ''%'+@Phone+'%'''
if(@MPhone<>'')
set @where=@where+' And MPhone like ''%'+@MPhone+'%'''
if(@IDCard<>'')
set @where=@where+' And IDCard like ''%'+@IDCard+'%'''
if(@startDate<>'' and @endDate<>'')
BEGIN
set @where=@where+' AND cast(datepart(m,cast(Birthday as datetime)) as varchar) between '+cast(datepart(m,cast(@startDate as datetime)) as varchar) + ' and '+cast(datepart(m,cast(@endDate as datetime)) as varchar)
set @where=@where+' AND cast(datepart(d,cast(Birthday as datetime)) as varchar) between '+cast(datepart(d,cast(@startDate as datetime)) as varchar) + ' and '+cast(datepart(d,cast(@endDate as datetime)) as varchar)
set @where=@where+' AND Birthday <>'''' '
END
set @sql=@sql+@where+' order by id desc'
exec(@sql)
END麻烦各位帮看下是什么问题
消息 242,级别 16,状态 3,第 1 行
从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。
ALTER PROCEDURE [dbo].[Customers_Search]
(
@CustomerName nvarchar(50),
@VIPNumber varchar(50),
@Worker nvarchar(50),
@Phone varchar(50),
@MPhone varchar(50),
@IDCard varchar(50),
@startDate varchar(50),
@endDate varchar(50)
)
AS
BEGIN
declare @sql varchar(2000),@where varchar(2000)
set @sql=' Select * From C_Customers '
set @where=' where 1=1 '
if(@CustomerName<>'')
set @where=@where+' And CustomerName like ''%'+@CustomerName+'%'''
if(@VIPNumber<>'')
set @where=@where+' And VIPNumber like ''%'+@VIPNumber+'%'''
if(@Worker<>'')
set @where=@where+' And Worker like ''%'+@Worker+'%'''
if(@Phone<>'')
set @where=@where+' And Phone like ''%'+@Phone+'%'''
if(@MPhone<>'')
set @where=@where+' And MPhone like ''%'+@MPhone+'%'''
if(@IDCard<>'')
set @where=@where+' And IDCard like ''%'+@IDCard+'%'''
if(@startDate<>'' and @endDate<>'')
BEGIN
set @where=@where+' AND cast(datepart(m,cast(Birthday as datetime)) as varchar) between '+cast(datepart(m,cast(@startDate as datetime)) as varchar) + ' and '+cast(datepart(m,cast(@endDate as datetime)) as varchar)
set @where=@where+' AND cast(datepart(d,cast(Birthday as datetime)) as varchar) between '+cast(datepart(d,cast(@startDate as datetime)) as varchar) + ' and '+cast(datepart(d,cast(@endDate as datetime)) as varchar)
set @where=@where+' AND Birthday <>'''' '
END
set @sql=@sql+@where+' order by id desc'
exec(@sql)
END麻烦各位帮看下是什么问题
@endDate datetime
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
变成
if(@startDate<>'' and @endDate<>'' and isdate(@startDate) and isdate(@endDate))
注意:传入endDate和startDate的时候一定要保证他们是日期格式
@startDate datetime
@endDate datetime
将exec(@sql) 改为select @sql 看看出来的动态查询是什么样,找出错误
对于Birthday字段和@startDate、@endDate两个变量,都是保证其值为日期类型,因为不太清楚有些客户的生日,所以其生日为空,如果Birthday使用DateTime类型的话,就不能写入空值,所以就用varchar(50)类型,在查询中转换即可。
set @where=@where+' AND cast(datepart(m,cast(Birthday as datetime)) as varchar) between '+cast(datepart(m,cast(@startDate as datetime)) as varchar) + ' and '+cast(datepart(m,cast(@endDate as datetime)) as varchar)
set @where=@where+' AND cast(datepart(d,cast(Birthday as datetime)) as varchar) between '+cast(datepart(d,cast(@startDate as datetime)) as varchar) + ' and '+cast(datepart(d,cast(@endDate as datetime)) as varchar)
set @where=@where+' AND Birthday <>'''' '
改成
set @where=@where+' AND cast(datepart(m,cast(Birthday as datetime)) as varchar) between cast(datepart(m,cast(' +@startDate+ ' as datetime)) as varchar) and cast(datepart(m,cast('+@endDate+ 'as datetime)) as varchar)'
set @where=@where+' AND cast(datepart(d,cast(Birthday as datetime)) as varchar) between cast(datepart(d,cast('+@startDate+' as datetime)) as varchar) and cast(datepart(d,cast('+@endDate+' as datetime)) as varchar)'
set @where=@where+' AND Birthday <>'''' '试试看