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