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

Popular posts from this blog

Calculating Age of the person in T-SQL

How to Troubleshoot Subscription issue in Reporting Services