I'm working with SQL Anywhere and I'm writting a function and have a little problem with converting ints (Hour and Minute) to time. Can you help me?
ALTER FUNCTION "DBA"."VisitTime"
RETURNS TIME
DETERMINISTIC
BEGIN
DECLARE "timeV" time
DECLARE hourV integer;
DECLARE minuteV integer;
DECLARE minutesV integer;
SELECT HOUR(CURRENT TIME) as hourV;
SELECT MINUTE(CURRENT TIME) as minuteV;
IF minuteV BETWEEN 0 and 29
THEN SET minutesV = 30;
END开发者_JAVA百科 IF;
IF minuteV between 30 and 59
THEN SET minutesV = 00;
SET hourV = hourV + 1;
IF hourV >= 24 THEN SET hourV = 0;
END IF;
END IF;
SET timeV = CAST(hourV || ':' || minutesV as time);
RETURN "timeV";
END;
What I do wrong? When I try to execute this I've got ERROR ("Result set not permitted in VisitTime")
I'm not a sqlanywhere guy, but deterministic means that given the same data and the same input/query you will get the same output. Said another way (SQL Server documentation)
Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
In your case your function, you reference CURRENT TIME, making the function NON-deterministic because the mere passing of time makes results differ.
精彩评论