将数据类型varchar转换为日期时间时出错。存储过程和两个函数

Question

Msg 8114，级别16，状态1，过程sp_wl_CalculateWorklistItemGroupTypeCurrentStats，行0批开始行619错误，将数据类型varchar转换为日期时间。

 exec sp_wl_CalculateWorklistItemGroupTypeCurrentStats @Practice_ID=20217,@WorkingDate='2022-06-02 14:59:04.4250000',@All=0

如何处理这种类型的日期WorkingDate？

我试过的是：

ALTER PROCEDURE [dbo].[sp_wl_CalculateWorklistItemGroupTypeCurrentStats] (
  @Practice_ID bigint,
  @WorkingDate datetime = NULL,
  @All bit = 0
)
AS 

  SELECT 
    WLIT.WorklistItemGroupTypeIdentify as ID,
    WLIT.Name as Prefix, 
    dbo.fn_wl_CalculateWorklistItemGroupTypeCurrentLoad(@Practice_ID, WLIT.WorklistItemGroupTypeIdentify, @WorkingDate) as CurrentLoad,
    dbo.fn_wl_CalculateWorklistItemGroupTypeCurrentCapacity(@Practice_ID, WLIT.WorklistItemGroupTypeIdentify, @WorkingDate ) as MaxCapacity
  FROM WorklistItemGroupType WLIT
  WHERE WLIT.PracticeIdentify = @Practice_ID
  AND WLIT.ReportOn = CASE @All WHEN 1 THEN WLIT.ReportOn ELSE 1 END

下面是返回Currentload结果的第一个函数。它们是数值。

ALTER FUNCTION [dbo].[fn_wl_CalculateWorklistItemGroupTypeCurrentCapacity] (
  @Practice_ID bigint,
  @GroupType_ID int,
  @WorkingDate datetime = NULL
)
RETURNS INT
AS
BEGIN 
  DECLARE @GlobalCapacity int = 0, @DayCapacity int = 0, @InstanceCapacity int = 0, @Result int = -1

  IF ISNULL(@WorkingDate, -1) = -1
    SET @WorkingDate = GETDATE()

  SET @WorkingDate = CAST(@WorkingDate as DATE) --remove time from datetime

  -- get capacity
  SELECT @GlobalCapacity = W.MaxCapacity, 
    @DayCapacity = CASE (@@datefirst - 1 + datepart(weekday, @WorkingDate)) % 7
      WHEN 0 THEN SundayCapacity
      WHEN 1 THEN MondayCapacity
      WHEN 2 THEN TuesdayCapacity
      WHEN 3 THEN WednesdayCapacity
      WHEN 4 THEN ThursdayCapacity
      WHEN 5 THEN FridayCapacity
      WHEN 6 THEN SaturdayCapacity
    END,
    @InstanceCapacity = COALESCE(Ext.MaxCapacity, 0)
  FROM WorklistItemGroupType W
  LEFT OUTER JOIN (SELECT TOP 1 WLIGTIdentify, MaxCapacity
                   FROM WorklistItemGroupTypeExtension 
                   WHERE WLIGTIdentify = @GroupType_ID
                   AND @WorkingDate BETWEEN TargetDateStart AND TargetDateEnd) as Ext ON (W.WorklistItemGroupTypeIdentify = Ext.WLIGTIdentify)
  WHERE WorklistItemGroupTypeIdentify = @GroupType_ID
  AND PracticeIdentify = @Practice_ID

  IF @InstanceCapacity > 0
    SET @Result = @InstanceCapacity
  ELSE IF @DayCapacity > 0
    SET @Result = @DayCapacity
  ELSE IF @GlobalCapacity > 0
    SET @Result = @GlobalCapacity;

  RETURN @Result
END

以下是第二个函数。预期将返回当前的最大容量。

ALTER FUNCTION [dbo].[fn_wl_CalculateWorklistItemGroupTypeCurrentLoad] (
  @Practice_ID bigint,
  @GroupType_ID int,
  @WorkingDate datetime = NULL
)
RETURNS INT
AS
BEGIN 
  DECLARE @CurrentLoad int = -1

  IF ISNULL(@WorkingDate, -1) = -1
    SET @WorkingDate = GETDATE()

  SET @WorkingDate = CAST(@WorkingDate as DATE) --remove time from datetime
  
  --get current load
  SELECT @CurrentLoad = COUNT(WLI.WorkListItemIdentify)
  FROM WorkListItem WLI
  INNER JOIN WorklistItemType WLIT ON (WLI.ItemTypeIdentify = WLIT.WorklistItemTypeIdentify) AND (WLIT.GroupTypeIdentify = @GroupType_ID)
  WHERE WLI.DateCreated BETWEEN @WorkingDate AND @WorkingDate + '23:59:59.997'
  AND WLI.PracticesIdentify = @Practice_ID
  AND WLI.Deleted = 0

  RETURN @CurrentLoad
END

Answer 1

日期文字末尾的0太多了。把它们移开，你就会没事的。

@WorkingDate='2022-06-02 14:59:04.425'

此外，还包括：

WLI.DateCreated BETWEEN @WorkingDate AND @WorkingDate + '23:59:59.997'

应该是这样：

WLI.DateCreated >= @WorkingDate AND WLI.DateCreated < DATEADD(day, 1, @WorkingDate)

最后，不是一个bug，但我会减少所有这些：

IF ISNULL(@WorkingDate, -1) = -1
  SET @WorkingDate = GETDATE()

SET @WorkingDate = CAST(@WorkingDate as DATE) --remove time from 
datetime

到目前为止：

Set @WorkingDate = CAST(COALESCE(@WorkingDate, current_timestamp) as Date)