ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

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

Updated on December 2, 2009

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

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Muhammad Saqib 3 years ago

      Nice Script

    • profile image

      Henri 4 years ago

      Great script.

    • profile image

      bp 4 years ago

      Perfect, this saved me LOTS of time! Thanks!

    • profile image

      Raúl 5 years ago

      Excelent