sp filtering dates using between causes error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp filtering dates using between causes error

Author

Topic

homerjay80
Starting Member

26 Posts

Posted - 2008-10-06 : 10:49:09

Hello,

I am trying to compile a SP that filter my results of a view by evaluating a date range using the Between keyword.

all three columns are formatted the same way, but the two values that make up the range are SP variables


---column from view
checkdate = CONVERT(DATETIME,SUBSTRING('2008080101',1,8),10)

--defined variables
select @startdate= CONVERT(DATETIME,SUBSTRING('2008010101',1,8),10)

select @enddate = CONVERT(DATETIME,SUBSTRING('200801201',1,8),10)

select checkdate from vw_data where 
		Code = 'CIP'
		AND checkdate
		BETWEEN  @Startdate
		AND @Enddate

Then when I try to create the SP, I get this error:


vw_data.checkdate' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause

Any idea why this occurs because all three date values seemed to be formatted the same way.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 10:56:14

the error is not in posted code. you seem to use GROUP BY in last select and seem like havent included checkdate in it. you need to apply aggregate functions over columns which are not specified in GROUP BY while grouping your results.

homerjay80
Starting Member

26 Posts

Posted - 2008-10-06 : 17:57:36

Ok, I think I fixed my aggregate problem, here is the error message I was referring to:

Server: Msg 256, Level 16, State 1, Procedure sp_200_Byte_Remit, Line 193
The data type datetime is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources