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

Time by any other name is still time, it just doesn't calculate so well.  After hours on my favorite search engine I thought I'd' share these functions to put a decimal 5.5 back to 5:30 and 7:30 back to 19.5.  you get the idea.

Enjoy

SQL Function to convert time to decimal

Click edit above to add content to this empty capsule.

 

/****** Object: UserDefinedFunction [dbo].[timeToDecimal] Script Date: 11/25/2009 08:51:54 ******/

SET

ANSI_NULLS 
ON

GO

SET

QUOTED_IDENTIFIER 
ON

GO

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

-- Author: 

-- Create date: 2009

-- Description: convert a time string to decimal

-- Parameter: datetime string (varchar) or just a time string

-- Example:

-- select dbo.TimeToDecimal('5:45:00:08') 

-- Returns: 5.75

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

CREATE

FUNCTION [dbo]. 
[timeToDecimal]

(

@timeToConvert 

varchar(25 
)

)

RETURNS

float

AS

BEGIN

DECLARE

@Result 
float

DECLARE

@t varchar(5 
)

SET

@t = cast(datepart(hh,@timeToConvert) as varchar(2)) + ':' + cast(datepart(n,@timeToConvert) as varchar(2 
))

SET

@Result = CAST(DATEDIFF(N, '00:00', @t) AS FLOAT)/ 
60

RETURN

@Result

END

SQL function to convert 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

More by this Author


Comments 4 comments

Raúl 4 years ago

Excelent


bp 4 years ago

Perfect, this saved me LOTS of time! Thanks!


Henri 4 years ago

Great script.


Muhammad Saqib 2 years ago

Nice Script

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working