Fix Bad Time - Data Quality Issue
CREATE FUNCTION [dbo].[ufn_utl_FixBadTime]
(
@StrTime VARCHAR(10)
)
RETURNS TIME
AS
BEGIN
DECLARE @TimeValue TIME
, @TimeR REAL;
SET @TimeR = CONVERT(REAL, @StrTime);
IF LEN(@StrTime) > 1
AND @TimeR < 24
BEGIN
DECLARE @Hr CHAR(2);
DECLARE @Min CHAR(2);
SET @Hr = RIGHT('00' + CONVERT(VARCHAR(5), ROUND(@TimeR, 0, 1)), 2);
SET @Min = RIGHT('00' + CONVERT(VARCHAR(5), CONVERT(INT, (@TimeR - ROUND(@TimeR, 0, 1)) * 60)), 2);
SET @TimeValue = CONVERT(TIME, @Hr + ':' + @Min);
END;
ELSE
BEGIN
SET @TimeValue = NULL;
END;
RETURN (@TimeValue);
END;
GO
Comments
Post a Comment