Convert Access Function to SQL

[Mich]

I'm going crazy trying to convert an Access Function to SQL.
From what I've read, it has to be done as a stored procedure.
I'm trying to take a field that is "minutes.seconds" and convert it to minutes.

This is what I have in Access:

Function ConvertToTime (myAnswer As Variant)
Dim myMinutes
myMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))
ConvertToTime =(myMinutes)
End Function

When I tried to modify it in SQL:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTime
Function ConvertToTime(myAnswer As Variant)
Dim myMinutes
myMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))
ConvertToTime=(myMinutes)
End

I get an error after ConverToTime.

[David Portas]

Transact-SQL is not VB!

If you are using SQL2000 you can create a user-defined function:

CREATE FUNCTION dbo.ConvertToMinutes (@minsec DECIMAL(5,2))
RETURNS DECIMAL(5,2)
BEGIN
RETURN ROUND(@minsec,0,1)+(@minsec-ROUND(@minsec,0,1))*10/6
END

GO

SELECT dbo.ConvertToMinutes(100.30)

Result:

-------
-100.50

(1 row(s) affected)

--
David Portas
------------
Please reply only to the newsgroup
--

[William Cleveland]

Mich wrote:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTime
Function ConvertToTime(myAnswer As Variant)
Dim myMinutes
myMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))
ConvertToTime=(myMinutes)
End

T-SQL uses "Return <value>" for functions, like C or Java, not
"<Function Name> = <value>", like VB (including Access) or Pascal.

Bill