Monday, April 4, 2011

Convert time stored as integer into hh:mm

The following T-SQL user defined function converts time stored as integer back to hh:mm formated string:

SET QUOTED_IDENTIFIER ON
GO

SET
ANSI_NULLS ON
GO

PRINT
'drop function [dbo].[udfConvert_To_Hours]'
GO

if
exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[udfConvert_To_Hours]')
and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[udfConvert_To_Hours]
GO

PRINT
'Create FUNCTION dbo.udfConvert_To_Hours'
GO

/******************************************************************************
NAME: udfConvert_To_Hours

PURPOSE:

REVISIONS:

$Log$

Ver Date Author Description
--------- ---------- --------------- ------------------------------------

******************************************************************************/
CREATE FUNCTION dbo.udfConvert_To_Hours(@pTime INT)
RETURNS NVARCHAR(10)
AS

BEGIN

DECLARE @Hours FLOAT
 ,@Minutes FLOAT
 ,@Result NVARCHAR(10)

SET @Result = N'0:00'

IF @pTime IS NOT NULL BEGIN
SET @Hours = CONVERT(Int,(@pTime / 60))
SET @Minutes = ROUND((CONVERT(FLOAT,@pTime) / 60 - @Hours) * 60,0)
SET @Result = CONVERT(NVARCHAR(10),@Hours) + ':'

IF @Minutes < 10
BEGIN
SET @Result = @Result + '0' + CONVERT(NVARCHAR(10),@Minutes)
END
ELSE
BEGIN
SET @Result = @Result + CONVERT(NVARCHAR(10),@Minutes)
END

END

RETURN(@Result)

END

GO

SET
QUOTED_IDENTIFIER OFF
GO

SET
ANSI_NULLS ON
GO

No comments:

Post a Comment