Error converting data type varchar to datetime. Stored procedure and two functions

Question

Msg 8114, Level 16, State 1, Procedure sp_wl_CalculateWorklistItemGroupTypeCurrentStats, Line 0 [Batch Start Line 619] Error converting data type varchar to datetime.

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

How can I handle this type of date WorkingDate?

What I have tried:

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

The following is the first function expected to return Currentload results. Which are numeric values.

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

The following is the second function. Is expected to return the current max capacity.

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

Too many 0s on the end of the date literal. Remove them and you should be fine.

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

Also this:

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

should be this:

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

Finally, not a bug, but I'd reduce all of this:

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

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

Down to this:

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