SQL 2005 functions to convert Time to decimal and decimal to Time

Click edit above to add content to this empty capsule.

/****** Object: UserDefinedFunction [dbo].[TimeFromDecimal] Script Date: 11/25/2009 08:53:42 ******/

SET

ANSI_NULLS 
ON

GO

SET

QUOTED_IDENTIFIER 
ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: 11/24/09

-- Description: Enter a decimal time and date

-- For example: 

--declare @decimalTime decimal(28,4), @dateTime datetime

--set @decimalTime = 5.668

--set @dateTime = '1/3/1900'

--

--select dbo.TimeFromDecimal( @decimalTime, @dateTime)

--Returns: 1900-01-03 05:40:04.800

-- =============================================

CREATE

FUNCTION [dbo]. 
[TimeFromDecimal]

(

@dTime 

decimal(28,4) 
--decimal Time

,@dateTime 
datetime

)

RETURNS

datetime

AS

BEGIN



-- Add the T-SQL statements to compute the return value here

DECLARE @iHour int, @iMin int, @iSec int, @imSec 
int

set @iHour = @dTime 
--concatenate hours

set @iMin = ((@dTime - @iHour)*60.) 
--subtract hours and convert to mins

--select mins and secs in dec form subtract mins and convert remainder to seconds:

set @iSec = (((@dTime-@iHour)*60-@iMin)*60 
)

set @imSec = ((((@dTime-@iHour)*60-@iMin)*60) - @iSec)* 
1000

if @iHour > 0
	begin
		set @dateTime = dateadd(hh, @ihour, @dateTime)
	end
	else --will subtract into the day before
	begin
		set @dateTime = dateadd(d, 1, @datetime)
		set @dateTime = dateadd(hh, @ihour, @dateTime)
	end	

set @dateTime = dateadd(mi, @imin, @dateTime 
)

set @dateTime = dateadd(s, @isec, @dateTime 
)

set @dateTime = dateadd(ms, @imsec, @dateTime 
)

-- Return the result of the function

RETURN 
@dateTime

END