469,613 Members | 1,645 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

Convert Access Function to SQL

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.
Jul 20 '05 #1
2 4809
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
--
Jul 20 '05 #2
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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Derek Riley | last post: by
12 posts views Thread by dixie | last post: by
9 posts views Thread by photomonkey | last post: by
3 posts views Thread by Ursula | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.