Function varchar to time conversion(hh:mm:ss) failed

Md Muntazim 0
Viewed 32
alter FUNCTION [dbo].[ConvertTimeToHH:MM:SS] 
(
    @P_Value NVARCHAR(50)
)
RETURNS NVARCHAR(50)
BEGIN
DECLARE @Result NVARCHAR(50)=''
DECLARE @Result1 NVARCHAR(50)=''
IF @P_Value !=''
BEGIN
    SET @Result = '(SELECT CONVERT(@P_Value,GETDATE(),108) as [hh:mm:ss])';
    RETURN @Result 
END
set @Result1='enter some value'

RETURN @Result1

END 


input: select [dbo].[ConvertTimeToHH:MM:SS] ('14')
output: the output shall be 14:00:00
input:select [dbo].[ConvertTimeToHH:MM:SS] ('14:3')
output: shall be 14:03:00

Select convert doesn't execute. What's wrong with this code please. When I execute this function I get output as:

(SELECT CONVERT(@P_Value,GETDATE(),108) as [hh:mm:ss])

sql-server sql
1 Answers

This will get it done, you will need to be responsible for validation. Assumption here is that data will have some format of nn:nn:nn. Anything outside of that, you'll need to rethink your solution.

ALTER FUNCTION [dbo].[ConvertTimeToHH:MM:SS] 
(
    @P_Value NVARCHAR(50)
)
RETURNS NVARCHAR(50)
BEGIN

IF @P_Value = ''
BEGIN
    RETURN 'enter some value'
END

DECLARE @hour VARCHAR(2) = ''
    , @min VARCHAR(2) = ''
    , @sec VARCHAR(2) = ''
    , @index INT

DECLARE @tmp VARCHAR(10)
    , @result VARCHAR(10);

SELECT @index = CHARINDEX(':', @P_Value)

-- only hour number is present
IF @index = 0
BEGIN
    SELECT @hour = @P_Value;
END
ELSE
BEGIN
    -- at least hour and minute is present
    SET @hour = LEFT(@P_Value, @index - 1);
    set @tmp = RIGHT(@P_Value, LEN(@P_Value) - @index);

    -- check if seconds is present
    SELECT @index = CHARINDEX(':', @tmp);

    IF @index = 0
    BEGIN
        SET @min = @tmp
    END
    ELSE
    BEGIN
        SET @min = LEFT(@tmp, @index - 1);
        SET @sec = RIGHT(@tmp, LEN(@tmp) - @index);
    END
END

SELECT @result = CONCAT(FORMAT(CAST(@hour AS INT), '00'), ':', FORMAT(CAST(@min AS INT), '00'), ':', FORMAT(CASt(@sec AS INT), '00'));
RETURN @result;

END 
GO

Results:

select [dbo].[ConvertTimeToHH:MM:SS] ('14')
select [dbo].[ConvertTimeToHH:MM:SS] ('1')
select [dbo].[ConvertTimeToHH:MM:SS] ('1:1')
select [dbo].[ConvertTimeToHH:MM:SS] ('1:1:1')


---------
14:00:00

---------
01:00:00

---------
01:01:00

---------
01:01:01
useroqMi0
Related