当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server 2005 - Conversion failed when converting datetime from character string

问题描述:

hi

i have a problem on converting datetime from character string on createddate and leavestartdate column.....

Table structure createdby varchar(30),createddate datetime, leavetype varchar(30), leavestartdate varchar(30), status varchar(30)

ALTER Procedure [dbo].[sp_SearchLeave]

@createdby varchar(30) = null ,

@createddate DateTime = null ,

@leavetype varchar(30) = null ,

@leavestartdate varchar(30) = null ,

@status varchar(30) = null

As

Begin

if @createddate is not null and Len(@createddate) = 0 set @createddate = null

if @leavetype = 'Select' set @leavetype = null

if @leavestartdate is not null and Len(@leavestartdate) = 0 set @leavestartdate = null

if @status = 'Select' set @status = null

Select leaverequestid,leaveenddate,leavetype,leavestartdate,status

from LeaveRequest

where

(@createdby is null or createdby like '%' + @createdby + '%')

and (@createddate is null or createddate like '%' + @createddate + '%')

and (@leavetype is null or leavetype like '%' + @leavetype + '%')

and (@leavestartdate is null or leavestartdate like '%' + @leavestartdate + '%')

and (@status is null or status like '%' + @status + '%')

End

when i execute this sp as a input like

exec sp_SearchLeave '','','','12/15/2010',''

it displays the error message like

Msg 241, Level 16, State 1, Procedure sp_SearchLeave, Line 26

Conversion failed when converting datetime from character string.

网友答案:

The reason is likely the like statement. You're treating a datetime parameter (@createddate) as a string by appending % to it, which you cannot do.

分享给朋友:
您可能感兴趣的文章:
随机阅读: