問題描述
我為營業時間計算制作了一個用戶定義的函數.
I made a user-define function for business hours calculation.
這是我的 UDF.
CREATE FUNCTION fn_GetBusinessHour (@date datetime, @addHours int)
RETURNS datetime
AS
BEGIN
DECLARE @CalcuatedDate datetime;
DECLARE @addDayCount int, @addHourCount int, @addMinCount int;
SET @addDayCount = @addHours / 8.5;
SET @addHourCount = @addHours - (@addDayCount * 8.5);
SET @addMinCount = @addHours - (@addDayCount * 8.5) - @addHourCount;
IF(@addDayCount != 0)
SET @CalcuatedDate = DATEADD(DD, @addDayCount, @date);
SET @CalcuatedDate = DATEADD(HH, @addHourCount, @CalcuatedDate);
IF(@addMinCount != 0)
SET @CalcuatedDate = DATEADD(MM, @addMinCount, @CalcuatedDate);
RETURN @CalcuatedDate;
END
當我使用以下語句進行測試時，
When I test using following statement,
SELECT dbo.fn_GetBusinessHour(GETDATE(), 40)
它顯示了正確的結果.
但是，我這樣使用我的函數，
However, I use my function like this,
SELECT TicketID
, DateTimeLogged --Type: Datetime
, Priority --Type: int
, [dbo].[fn_GetBusinessHour](DateTimeLogged, Priority)
FROM TicketHeader
結果只顯示NULL值.
the result shows only NULL value.
TicketID DateTimeLogged Priority (No column name)
1 2011-07-04 11:26:19.510 30 NULL
2 2011-07-04 13:58:45.683 30 NULL
3 2011-07-05 10:09:16.923 10 NULL
4 2011-07-05 13:13:30.237 30 NULL
5 2011-07-05 16:50:34.033 20 NULL
我嘗試了 CONVERT，因為當我給出值 40 時它起作用，但它也顯示空值.
I tried CONVERT because it worked when I give a value 40 but it also shows null values.
SELECT TicketID
, DateTimeLogged --Type: Datetime
, Priority --Type: int
, [dbo].[fn_GetBusinessHour](DateTimeLogged, CONVERT(int, Priority))
FROM TicketHeader
我該如何解決這個問題才能讓我的 UDF 正常工作?為什么會發生這件事?我無法理解 Priority 和 40 之間有什么不同.
How can I fix this to work my UDF? Why this thing happen? I cannot understand what is different between Priority and 40.
提前致謝.
推薦答案
對于優先級 > 8.5 的值，這對我來說似乎很好:
For values of priority > 8.5, this seems to work fine for me:
DECLARE @t TABLE(TicketID INT, DateTImeLogged DATETIME, Priority INT);
INSERT @t SELECT 1,'20110704 11:26:19.510',30
UNION ALL SELECT 2,'20110704 13:58:45.683',30
UNION ALL SELECT 3,'20110705 10:09:16.923',10
UNION ALL SELECT 4,'20110705 13:13:30.237',30
UNION ALL SELECT 5,'20110705 16:50:34.033',20;
SELECT TicketID
, DateTimeLogged --Type: Datetime
, Priority --Type: int
, [dbo].[fn_GetBusinessHour](DateTimeLogged, Priority)
FROM @t;
產量:
TicketID DateTimeLogged Priority (No column name)
-------- ----------------------- -------- -----------------------
1 2011-07-04 11:26:19.510 30 2011-07-07 15:26:19.510
2 2011-07-04 13:58:45.683 30 2011-07-07 17:58:45.683
3 2011-07-05 10:09:16.923 10 2011-07-06 11:09:16.923
4 2011-07-05 13:13:30.237 30 2011-07-08 17:13:30.237
5 2011-07-05 16:50:34.033 20 2011-07-07 19:50:34.033
如果我添加另一行的優先級 <8.5，例如:
If I add another row with a Priority < 8.5, e.g.:
INSERT @t SELECT 6,'20110705 13:13:30.237',5;
然后將這一行添加到結果中:
Then this row is added to the result:
TicketID DateTimeLogged Priority (No column name)
-------- ----------------------- -------- -----------------------
6 2011-07-05 13:13:30.237 5 NULL
換句話說，如果函數邏輯讓@CalculatedDate 未賦值，則該函數將輸出NULL，如果@addDayCount = 0，就會發生這種情況.在您說的函數中:
In other words, the function will output NULL if the function logic leaves @CalculatedDate unassigned, which will happen if @addDayCount = 0. In the function you say:
IF(@addDayCount != 0)
SET @CalcuatedDate = DATEADD(DD, @addDayCount, @date);
因為@addDayCount 是一個 INT，試試這個:
Since @addDayCount is an INT, try this:
DECLARE @addDayCount INT;
SET @addDayCount = 5 / 8.5;
SELECT @addDayCount;
結果:
0
因此，因為@CalculatedDate 最初沒有分配值，所以以下所有 DATEADD 操作都在執行 DATEADD(interval, number, NULL) ，但仍會產生 NULL.
So because @CalculatedDate isn't assigned a value initially, all of the following DATEADD operations are performing DATEADD(interval, number, NULL) which still yields NULL.
所以也許你需要為函數中的變量使用不同的數據類型...
So perhaps you need to use a different data type for the variables in the function...
這篇關于在 SELECT 語句中使用 UDF的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！