Category Archives: Master_admin

CREATE FUNCTION [dbo].[fn_RunDuration] (@nRunDuration INT)
RETURNS VARCHAR(128)
AS 
BEGIN 
    DECLARE @vcMsg VARCHAR(128),
        @nH INT,
        @nM INT,
        @nS INT,
        @vcH VARCHAR(48),
        @vcM VARCHAR(48),
        @vcS VARCHAR(48)
    SELECT  @nH = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 6), 2),
            @nM = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 4), 2),
            @nS = RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 2),
            @vcH = CASE WHEN @nH = 0 THEN ''
                        WHEN @nH = 1 THEN CAST(@nH AS VARCHAR) + ' hour, '
                        ELSE CAST(@nH AS VARCHAR) + ' hours, '
                   END,
            @vcM = CASE WHEN @nM = 0 THEN ''
                        WHEN @nM = 1 THEN CAST(@nM AS VARCHAR) + ' minute, '
                        ELSE CAST(@nM AS VARCHAR) + ' minutes, '
                   END,
            @vcS = CASE WHEN @nS = 1 THEN CAST(@nS AS VARCHAR) + ' second'
                        ELSE CAST(@nS AS VARCHAR) + ' seconds'
                   END,
            @vcMsg = @vcH + @vcM + @vcS

    RETURN @vcMsg
END

CREATE FUNCTION [dbo].[fn_INTtoDateTime] (
     @vcDate VARCHAR(8),
     @vcTime VARCHAR(6)
    )
RETURNS DATETIME
AS 
BEGIN
    SET @vcDate = RIGHT(RTRIM('00000000' + CONVERT(CHAR(8), @vcDate) + ' '), 8)
    SET @vcTime = RIGHT(RTRIM('00000000' + CONVERT(CHAR(6), @vcTime) + ' '), 6)

    IF @vcDate = '00000000' 
        SET @vcDate = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), CAST(0 AS SMALLDATETIME), 121),
                                                   '-', ''), ':', ''),
                                   SPACE(1), ''), 8)
	
    RETURN (  
	CAST(SUBSTRING(@vcDate,1,4) + '-' + SUBSTRING(@vcDate,5,2) + '-' + SUBSTRING(@vcDate,7,2) + ' ' + 
		SUBSTRING(@vcTime,1,2) + ':' + SUBSTRING(@vcTime,3,2) + ':' + SUBSTRING(@vcTime,5,2) 
	AS DATETIME)
	)
END