Using Like statement in parameters

[Jason Stinson]

I have a stored procedure that I need to modify and I would like to use the LIKE clause instead of the BETWEEN in a date range. I am running a report that calls this stored procedure and right now it is using @StartDate and @EndDate. I want to use LIKE. here is a sample of what I am wanting to change.

SELECT @Contracts = Count(AppID) 
FROM #TEMP 
WHERE Dealer = @vDealer 
AND StatusAppDetail = 'A' 
AND (DateContractFunded between @StartDate AND @EndDate)

I have a parameter in a report that calls the stored procedure and puts a date range which replaces the StartDate and EndDate. It is not pulling all the records for the date range because for some reason it cuts off after 7:00 pm and doesn't pull everything for the daterange. When I use the LIKE clause it works fine. Any help woulde be appreciated.



Jason

[code green]

Your second paragraph seems to contradict the first.
It works with LIKE but you want to use LIKE?

iS DateContractFunded a DATETIME data type?

[Jason Stinson]

Ok, I guess I got too excited asking my question. The DateContractFunded is a DATETIME data type. I have used several different ways to pull the data but for some reason, it only pulls all the data using the LIKE clause. I want to get rid of the BETWEEN and use the LIKE to pull a date range. When I run my report, there is a @StartDate and @EndDate parameter which populates the @StartDate and @EndDate in the stored procedure. I don't know of any other way to pull a date range using a parameter in BO that will give me all the records. What I want is all the records in a date range tha I specify so if I want everything form 1-1-2011 to 1-31-2011 I want every single record with that date range regardless of the time stamp. If ther is another way or something easier, please inform me. I am struggling here! Thanks.

[ck9663]

Here, start with this.

Happy Coding!!!

~~ CK

[bvrwoo]

You must cast it to datetime because my default the DBO will save it as a text.

[NeoPa]

You're using @StartDate and @EndDate, but you really want to specify DateTimes. It's natural you'll struggle if your names don't reflect what you're trying to achieve.

As a Date value reflects the time at midnight at the start of that date you probably want to ensure the @StartDate (DateTime) value is set to midnight of the start date of the range, and the @EndDate (DateTime) value is set to midnight of the day following the date range. Not too many real-life DateTime values turn up as midnight (not in most businesses anyway).