471,863 Members | 1,135 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,863 software developers and data experts.

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 4898
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
NeoPa
reply views Thread by NeoPa | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.