when we use datediff function in SQL to determine how log something is running, we have to make the choice how to represent it. If we choose minutes, SQL will round the datediff time. If we choose seconds, we'll have to determine how many minutes + some seconds this was For Example 5315027 seconds means 12:23:47 i.e 12 hours 23 minutes and 47 seconds which is more understandable than 5315027
we can write query as
select CONVERT(varchar(8),DATEADD(second,datediff(ss,GetDate(),Convert(Datetime,'07-07-10')) ,0),114)
or we can create a UDF like that
create function [dbo].[SecondsToText](@seconds int) returns varchar(8)
as
begin
return (select CONVERT(varchar(8),DATEADD(second,@seconds,0),114))
end
go
Same can be Used with Minutes but of course with Slight change in parametes
select CONVERT(varchar(8),DATEADD(mi,datediff(mi,GetDate(),Convert(Datetime,'07-07-10')) ,0),114)
or a Function like that
create function [dbo].[MinutesToText](@minutes int) returns varchar(8)
as
begin
return (select CONVERT(varchar(8),DATEADD(mi,@minutes,0),114))
end
go
No comments:
Post a Comment