Help with SQL Date conversion

Posted on 2011-10-28
Medium Priority
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

Lee Savidge earned 2000 total points
ID: 37045595
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 27

Expert Comment

by:Shaun Kline
ID: 37045647
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

ID: 37045652
Thank you!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 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