Time parameter Issue

[Luther1978]

I'm passing an SQL String into another Stored procedure, everything is working great until I add the date parameters. After much fiddling and lots of trawling the Internet I found the means to convert the date parameters and pass them onto the other Stored Procedure without error. The result was an empty view returned when I know there should be data for the dates entered.
I have tried both using the "WHERE BETWEEN" Method and the "<time column> =>FromTime AND <= ToTime" Method but both are returning nothing. However if I just use one criteria on the time "<time column> =>FromTime" then I get some data returned. Whats going on?

<code>
CREATE PROCEDURE WAMS_ClockCardByParameter @ContractID int, @DepotID int, @DateFrom SMALLDATETIME, @dateto SMALLDATETIME AS
DECLARE @SQL varchar(560)

SET @SQL = "SELECT CostCode, ResourceName, DepotID,StartDateTime FROM View_ClockCard
INNER JOIN View_ClockCardDays ON (View_ClockCard.AllocationID=View_ClockCardDays.AllocationID)
WHERE StartDateTime BETWEEN " + Convert(varchar(10),@DateFrom,120) + " AND "+ Convert(varchar(10),@DateTo,120) + " AND ContractID = "+ CONVERT(varchar(2), @ContractID)+ " AND DepotID = "+CONVERT(varchar(2), @DepotID)+ "
Group BY CostCode, ResourceName, ContractID, DepotID, StartDateTime"

EXEC WAMS_crosstab @SQL, 'SUM(Hours)', 'Day', 'View_ClockCardDays'
GO
<Code End>

Many Thanks

Martin

 

[mrdenny]

It would be easier to have the @DateFrom and @DateTo variables as varchar(10) to begin with. What values are you passing to the WAMS_ClockCardByParameter when you run it?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[SQLBill]

Is your SQL Server instance/database CASE SENSITIVE?

In the DECLARE you have @dateto
but in the script you have @DateTo.

With CASE SENSITIVE they are not the same thing.

-SQLBill

Posting advice: FAQ481-4875

 

[Luther1978]

Many Thanks guys, for your replies. SQLBill I hadn't noticed the change in case ono my variables, but this didn't solve the problem.

I have managed to solve the problem, by printing the SQL string out as it ran I eventually realised that I needede to include ' ' around the dates. Works a treat now.

Thank you both very much for your help

Martin