Help with SQL Date conversion

Hello, I need some help with the code below. The value I assign to @SearchDate is not bringing back values with my statement but there is a closedate that matches @searchdate

This is the closedate value in the database:

2010-10-08 08:16:26.983

This is format of the date I'm passing in:
10/08/2011

In my Where clause I'm attempting to convert closedate to = @Searchdate but nothing is returned...

What am I doing wrong?
declare @Division  varchar(1) set @Division = ''
declare @Status  varchar(1) set @Status = ''
declare @BAnumber varchar(12) set @BANumber = ''
declare @Ticket char (12) set @Ticket = ''
declare @InvoiceNumber char (12) set @InvoiceNumber = ''
declare @SearchDate char (12) set @SearchDate = '10/08/2011'

Select BANumber, Sub, BAAMT, Division, Facility, Ticket,
ltrim(Substring(createdBy, charindex('\', createdBy) + 1 , 
Len(createdBy) - charindex('\', createdBy) - 0)) as CreatedBy ,
CONVERT(varchar(15), [createdate], 101)as [createdate], [status],
CONVERT(varchar(15), [CloseDate], 101)as [CloseDate], 
ltrim(Substring(closedBy, charindex('\', closedBy) + 1 , 
Len(closedBy) - charindex('\', closedBy) - 0)) as closedBy, InvoiceNumber as InvoiceNumber
from BA

Where
(rtrim(@Status)  is NUll or rtrim([Status])  like rtrim(@Status) + '%')
and (rtrim(@BANumber)  is NUll or rtrim(BANumber)  like rtrim(@BANumber) + '%')
and (rtrim(@Ticket)  is NUll or rtrim(Ticket)  like rtrim(@Ticket) + '%')
and (rtrim(@InvoiceNumber)  = '' or rtrim(InvoiceNumber)  like rtrim(@InvoiceNumber) + '%')
and (rtrim(@Division)  is NUll or rtrim(Division) like rtrim(@Division) + '%')
and (rtrim(@SearchDate)  is NUll or CONVERT(varchar(15), [CloseDate], 101) like rtrim(@SearchDate) + '%')

Open in new window

gogetsomeAsked:
Who is Participating?
 
Lee SavidgeConnect With a Mentor Commented:
It isn't retruning anything because the date in the database doesn't match the date your passing in. The dates might be the same but the date AND TIME are not.

You where clause should truncate the time:

and (rtrim(@SearchDate)  is NUll or CONVERT(varchar(10), [CloseDate], 101) like rtrim(@SearchDate) + '%')
0
 
Shaun KlineLead Software EngineerCommented:
Your @SearchDate is a char(12), while the format for converting dates using 101 is a char(10).
Hence you are comparing [10/18/2011] with [10/18/2011  %].
0
 
gogetsomeAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.