Shulou(Shulou.com)06/01 Report--

Hello, Mr. Han has a question for you. I want to calculate overtime by seconds. The rule is: overtime is 4 hours, overtime is 8 hours, overtime is less than 4 hours, overtime is less than 4 hours, overtime is 0.5 hours. I wrote a custom function to calculate it, but the result was not correct. Trouble Mr. Han for advice.

CREATE FUNCTION worktime

(@ startTime int)

RETURNS nvarchar (100)

AS

BEGIN

Declare @ rst nvarchar

Declare @ day int

Declare @ hours int

Declare @ aa decimal

Set @ rst=''

Set @ day=@startTime/28800-- 8 hours a day, 8 hours, 28800 seconds.

Set @ hours=@startTime% 28800 Compact 3600-less than 8 hours of calculation, how many hours is the extra time

Set @ aa=0.5-there is no need for constants to create variables

If (@ day > 0)-there is no need to judge

Set @ rst=@day

If (@ hours=4)-just need to determine whether it is greater than or equal to 4 hours

Set @ rst=@rst+@aa-@ rst is a character type that cannot be added to @ aa

RETURN @ rst

After the above function is created, the execution result is not as expected select dbo.worktime (28800014300), the result is 1 select dbo.worktime (2880014500), the result is 2.

I changed and streamlined the function.

CREATE FUNCTION worktime

(@ startTime int)

RETURNS nvarchar (100)

AS

BEGIN

Declare @ rst nvarchar

Declare @ day int

Declare @ hours int

Set @ rst=''-- can be initialized for assignment

Set @ day=@startTime/28800-calculate the number of overtime days. Since @ day is of type int, the result will automatically take the integer part.

Set @ hours=@startTime% 28800 Compact 3600-calculate the advance

If (@ hours > = 4)-half-day if overtime exceeds 4 hours

Set @ rst=@day+0.5

Else set @ rst=@day-otherwise, overtime will not be counted.

RETURN @ rst

End

End

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.