Date Conversion Help

Here is my query
SELECT TOP 1000 Team, Segment, Associate, SUM([Adjusted Weight]) AS
[SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt,
SUM(VerSatWt) AS SumOfVerSatWt, COUNT([Surv Strt Tm])
AS [CountOfSurv Strt Tm], [Date]
FROM dbo.CSI
WHERE ([Date] >= 'dbo.Getparams.startdate') AND ([Date] <= 'dbo.Getparams.
Enddate')
I'm trying to pass the startdate from the table to my query and get a syntax
error converting character string to smalldatetime data. I have changed the
startdate field in the getparams table to Char, then datetime, then Varchar
and tried convert and replace convert, and still not getting it to pass. Yet
if I hard code the date say '05/01/2006' (which is the same as in the table)
the query works fine.
What to do? Thanks for your help in advance!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1> SELECT TOP 1000 Team, Segment, Associate, SUM([Adjusted Weight]) AS
> [SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt,
> SUM(VerSatWt) AS SumOfVerSatWt, COUNT([Surv Strt Tm])
> AS [CountOfSurv Strt Tm], [Date]
> FROM dbo.CSI
> WHERE ([Date] >= 'dbo.Getparams.startdate') AND ([Date] <=
> 'dbo.Getparams.
> Enddate')

> I'm trying to pass the startdate from the table to my query and get a
> syntax
> error converting character string to smalldatetime data.
If you have a single row in th GetParams table, then try:
DECLARE @.sd SMALLDATETIME, @.ed SMALLDATETIME;
SELECT @.sd = StartDate, @.ed = EndDate FROM dbo.GetParams;
SELECT ... WHERE [Date] >= @.sd AND [Date] <= @.ed;
The way you've shoved a reference to a table name into a place that expects
a scalar value (or a column that comes from a table that actually
participates in the query) does not work.
Also, what TOP 1000 do you expect? Unless you don't care that you could get
a different result and in a different order every time you run the query,
please don't use TOP without ORDER BY. I'd also suggest avoiding the use of
reserved words or words with spaces as column names.

> if I hard code the date say '05/01/2006' (which is the same as in the
> table)
NO IT IS NOT! SQL Server does not store such a regional and ambiguous
format. In fact, it does not store a string at all, but rather a pair of
integers.
http://www.karaszi.com/SQLServer/info_datetime.asp|||It takes a SELECT statement to get data out of a table. Your
dbo.GetParams.StartDate and dbo.GetParams.EndDate are just idle references
at this point.
You could either obtain the values and put them in variables, or perhaps
create a function that would get them for you. (The function is useful if
you often need the Start/End dates from the GetParams table.
Arnie Rowland, PhD
"To be successful, your heart must accompany your knowledge."
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:62833dcfeea64@.uwe...
> Here is my query
> SELECT TOP 1000 Team, Segment, Associate, SUM([Adjusted Weight]) AS
> [SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt,
> SUM(VerSatWt) AS SumOfVerSatWt, COUNT([Surv Strt Tm])
> AS [CountOfSurv Strt Tm], [Date]
> FROM dbo.CSI
> WHERE ([Date] >= 'dbo.Getparams.startdate') AND ([Date] <=
> 'dbo.Getparams.
> Enddate')
> I'm trying to pass the startdate from the table to my query and get a
> syntax
> error converting character string to smalldatetime data. I have changed
> the
> startdate field in the getparams table to Char, then datetime, then
> Varchar
> and tried convert and replace convert, and still not getting it to pass.
> Yet
> if I hard code the date say '05/01/2006' (which is the same as in the
> table)
> the query works fine.
> What to do? Thanks for your help in advance!!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1