Conversion failed when converting datetime from character string

Question

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.

Bradford's answer is undoubtedly correct, but it's also worth knowing that you're using an unsafe string format for dates also - the safe format for date (without time) is YYYYMMDD, e.g. '20101215' — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 16, 2010 at 14:25
@brad Thats right but i passing a date from front end ASP.net.... — Raj
– Raj, Commented Dec 16, 2010 at 14:29

Bradford Hoagland · Accepted Answer · 2010-12-16 14:18:43Z

3

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.

answered Dec 16, 2010 at 14:18

Bradford Hoagland

2912 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bradford Hoagland Over a year ago

I'm not really sure what you are attempting to do. Performing "like" statement on a date, even one converted to a string, is unusual. Are you attempting to search for a specific month, year, or other date part?

Bradford Hoagland Over a year ago

That part is understandable. What is this "like" clause used for? Are you attempting to ignore that time portion of the column in the database? If so, then you can compare the date on a single-day range [e.g., DateColumn >= @DateParameter and DateColumn < dateadd(day, 1, @DateParameter)]

Raj Over a year ago

okay if i going to pass the date into database then it should search the exact match date and retrieve the record this what i want.... can u understand this one.

Bradford Hoagland Over a year ago

As in simply, DateColumn = @DateParameter? If so, then there is no need to manipulate the date parameter at all, unless there is any specific validation of the parameter needed. So, you can drop the "like" operator and go with a straight equality comparison.

Raj Over a year ago

@brad : The straight equality comparison doesn't work but the DateColumn >= @DateParameter and DateColumn < dateadd(day, 1, @DateParameter)] this will works fine.. Thanks for ur answer Friend.

Collectives™ on Stack Overflow

Conversion failed when converting datetime from character string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related