Date Conversion Help

29 Jun 2006 8:21 PM

Chamark via SQLMonster.com

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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200606/1

29 Jun 2006 8:38 PM

Aaron Bertrand [SQL Server MVP]

> 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

30 Jun 2006 5:24 AM

Arnie Rowland

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 SQLMonster.com" <u21870@uwe> wrote in message

news:62833dcfeea64@uwe...
Show quote

> 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 SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200606/1