Monday, April 4, 2011

Convert time in hh:mm to integer value

The following T-SQL user defined function convert time in hh:mm to integer value:


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
PRINT 'drop function [dbo].[udfConvert_To_Minutes]'
GO
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[udfConvert_To_Minutes]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfConvert_To_Minutes]
GO
PRINT 'CREATE FUNCTION dbo.udfConvert_To_Minutes'
GO
/******************************************************************************

NAME: udfConvert_To_Minutes
PURPOSE:
REVISIONS:
$Log$
Ver Date Author Description

--------- ---------- --------------- ------------------------------------
******************************************************************************/
CREATE FUNCTION dbo.udfConvert_To_Minutes(@pTime DATETIME)
RETURNS FLOAT
AS
BEGIN
DECLARE @vHours FLOAT
 ,@vMinutes FLOAT
 ,@vTime nvarchar(30)
 ,@Result FLOAT
SET @Result = 0
SET @vTime = CONVERT(nvarchar(30),@pTime,8)
SET @vHours = CONVERT(INT,(SUBSTRING(@vTime,1,2)))
SET @vMinutes = CONVERT(INT,(SUBSTRING(@vTime,4,2)))
SET @Result = @vHours * 60 + @vMinutes
RETURN(@Result)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
If you find this script useful please donate generously.

No comments:

Post a Comment