Change Access-function to SQL Server function

Tiger955

Registered User.
Local time
Today, 14:55
Joined
Sep 13, 2013
Messages
140
Hi!

I hope someone can help me in that case.

I am migrating as much as possible from Access front end to SQL-Server. Same with functions.

I am not able to convert this function into a scalar function on SQLS 2008:

Function fncRoundToHourParts(dteWorkingtime As Date, x As Integer)
'rounds to parts of hours:x=4 to 15 minutes, x=6 to 10 min., x=12 to 5 min.
fncRoundToHourPartsArbeitszeit = Format(Round(dteWorkingtime * 24 / (1 / x), 0) * (1 / x) / 24, "dd.mm.yyyy hh:nn")
End Function

example:
fncRoundToHourParts(#2014-02-12 10:18:15#,4)
12.02.2014 10:15


I tried:
create FUNCTION dbo.RoundToHourParts
(@dteWorkingtime nvarchar(23), @x int)

RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar datetime;
 
-- Add the T-SQL statements to compute the return value here
SELECT @ResultVar=Round(CAST(CONVERT(datetime,@dteWorkingtime ) as float) * 24 / (1 / 4), 0) * (1 / 4) / 24
-- Return the result of the function
RETURN @ResultVar
END
GO

and many other things, I cannot solve it.

Hope someone can help me!
Thanks
Michael
 
You might try:
Code:
[COLOR="Blue"]CREATE FUNCTION[/COLOR] dbo.RoundToHourParts (
	@dteWorkingtime [COLOR="blue"]nvarchar[/COLOR](23),
	@x [COLOR="blue"]float[/COLOR])
[COLOR="blue"]RETURNS datetime
AS
BEGIN[/COLOR]
	[COLOR="DarkGreen"]-- Declare the return variable here[/COLOR]
	[COLOR="blue"]DECLARE[/COLOR] @ResultVar [COLOR="blue"]datetime[/COLOR];
	[COLOR="darkgreen"]-- Add the T-SQL statements to compute the return value here[/COLOR]
	[COLOR="blue"]SELECT[/COLOR] @ResultVar = [COLOR="Magenta"]CONVERT[/COLOR]([COLOR="blue"]datetime[/COLOR], [COLOR="magenta"]ROUND[/COLOR](
		[COLOR="magenta"]CAST[/COLOR]([COLOR="magenta"]CONVERT[/COLOR]([COLOR="blue"]datetime[/COLOR], @dteWorkingtime) [COLOR="blue"]as float[/COLOR]) * 24 / (1 / @x), 0)
		* (1 / @x)/ 24);
	[COLOR="darkgreen"]-- Return the result of the function[/COLOR]
	[COLOR="blue"]RETURN[/COLOR] @ResultVar;
[COLOR="blue"]END
GO[/COLOR]
 
I played with this a little yesterday and my solution had the same problem Byte's does. Many values are correct, but a rounding issue exists, as I get a few like

2014-02-14 13:44:59.997

I wasn't sure how to beat it and didn't have time to pursue it.
 
Sorry, the return type should be type SMALLDATETIME, thus:
Code:
[COLOR="Blue"]CREATE FUNCTION[/COLOR] dbo.RoundToHourParts (
	@dteWorkingtime [COLOR="blue"]nvarchar[/COLOR](23),
	@x [COLOR="blue"]float[/COLOR])
[COLOR="blue"]RETURNS smalldatetime
AS
BEGIN[/COLOR]
	[COLOR="DarkGreen"]-- Declare the return variable here[/COLOR]
	[COLOR="blue"]DECLARE[/COLOR] @ResultVar [COLOR="blue"]smalldatetime[/COLOR];
	[COLOR="darkgreen"]-- Add the T-SQL statements to compute the return value here[/COLOR]
	[COLOR="blue"]SELECT[/COLOR] @ResultVar = [COLOR="Magenta"]CONVERT[/COLOR]([COLOR="blue"]smalldatetime[/COLOR], [COLOR="magenta"]ROUND[/COLOR](
		[COLOR="magenta"]CAST[/COLOR]([COLOR="magenta"]CONVERT[/COLOR]([COLOR="blue"]datetime[/COLOR], @dteWorkingtime) [COLOR="blue"]as float[/COLOR]) * 24 / (1 / @x), 0)
		* (1 / @x)/ 24);
	[COLOR="darkgreen"]-- Return the result of the function[/COLOR]
	[COLOR="blue"]RETURN[/COLOR] @ResultVar;
[COLOR="blue"]END
GO[/COLOR]
 
Ah! I had the idea it was something simple but I haven't written that many functions in T-SQL and that didn't dawn on me.
 

Users who are viewing this thread

Back
Top Bottom