Help with SQL Date conversion

Posted on 2011-10-28
Last Modified: 2012-06-21
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:

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

(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

Question by:gogetsome
    LVL 25

    Accepted Solution

    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) + '%')
    LVL 25

    Expert Comment

    by:Shaun Kline
    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  %].

    Author Closing Comment

    Thank you!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how the fundamental information of how to create a table.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now