Dharmesh Sharma

Dharmesh Sharma

  • NA
  • 905
  • 927.2k

how to use function in sql with select query

Jan 24 2011 5:52 AM
hi 

i m create a function which is return the total time in my formate when i post a request

function is 

 ALTER FUNCTION [dbo].[Udf_Get_Posting_Time_To_Up] 
(
	
	@postingdate			datetime
)
RETURNS VARCHAR(MAX)
AS
BEGIN

declare @start datetime, @end datetime, @duration varchar(36),
@hours varchar(30), @minutes varchar(2), @seconds varchar(30),@days varchar(30)
set @start =getdate() 
set @end = @postingdate 
set @seconds = abs(datediff(second, @start, @end))
SET @days=@seconds/(3600*24)
set @hours = (@seconds-(@days*3600*24))/3600
set @minutes = (@seconds - (@hours * 3600)-(@days*3600*24)) / 60
set @seconds = (@seconds - (@hours * 3600) - (@minutes * 60)-(@days*3600*24))
if cast(@hours as int) < 10 
	set @hours = '0' + @hours
if cast(@minutes as int) < 10 
	set @minutes = '0' + @minutes
if cast(@seconds as int) < 10 
	set @seconds = '0' + @seconds
set @duration =@days +' Days '+@hours + ' Hrs ' + @minutes + ' Min ' + @seconds+' Sec '
RETURN @duration

END


now i pass the date of posting and get total time 


i use this function in another storeproducer like this 

SELECT id,username, Udf_Get_Posting_Time_To_Up(date) FROM articles


i m geting this errer

'Udf_Get_Posting_Time_To_Up' is not a recognized built-in function name.


please help me

Answers (2)