Error 116 - Function Help Please

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)

Error 116 - Function Help Please

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-24 : 05:21:41

Hello,

I am having a problem with a function I'm trying to create.
Basically because of problems with dates and return values of stored procedures having to be an int, I'm trying to pass a date back in ticks as opposed to the actual date.

I've found a function that will convert the date value into the number of ticks (see link), but for some reason I am getting the following error when I try to use it in my function:

Link = [url]http://www.codeproject.com/KB/database/DateTimeToTicks.aspx[/url]

"Error 116: only one expression can be specified in the select list when the subquery is not introduced with EXISTS"

The code in my function is as follows :


CREATE FUNCTION dbo.fn_YearBreakdownDateFrom (@Date datetime) 
RETURNS float
AS
BEGIN 
  DECLARE	@RetVal datetime
  DECLARE 	@ConvertedDate float
  SET @RetVal = (SELECT  DateFrom
  FROM      dbo.tbl_YearBreakdown
  WHERE     (DateFrom <= CONVERT(DATETIME, @Date, 103)) AND
            (DateTo >= CONVERT(DATETIME, @Date, 103)))
  SET @ConvertedDate =(SELECT @RetVal, dbo.fn_DateToTicks(@RetVal))
  RETURN @ConvertedDate
END

Can anyone help please ?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 05:34:50

CREATE FUNCTION dbo.fn_YearBreakdownDateFrom (@Date datetime)
RETURNS float
AS
BEGIN
DECLARE @RetVal datetime
DECLARE @ConvertedDate float
SET @RetVal = (SELECT DateFrom
FROM dbo.tbl_YearBreakdown
WHERE (DateFrom <= CONVERT(DATETIME, @Date, 103)) AND
(DateTo >= CONVERT(DATETIME, @Date, 103)))
SET @ConvertedDate =(SELECT ~~@RetVal,~~ dbo.fn_DateToTicks(@RetVal))
RETURN @ConvertedDate
END
ur mentioning the two parameters in subquery so use one parameter while assign to a value

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-02-24 : 06:01:04

Thanks bklr - As it happens I've managed to get this to work.

It was to do with the function expecting the 3 parameters in the right order etc.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 23:28:05

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources