Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Wednesday, May 29, 2013

SQL Function to Convert Seconds to HH:MM


Create a function as like below:

CREATE FUNCTION [dbo].[ufn_GetHHMM] ( @pInputSecs   BIGINT )
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @HHMM       Varchar(Max)

IF @pInputSecs < 60 and @pInputSecs<>0
BEGIN
SET @HHMM= '0:01'
END
ELSE
BEGIN
SET @HHMM=ISNULL(CAST(@pInputSecs/3600 AS VARCHAR(MAX)) +':'
+RIGHT('0'+CAST(ROUND(CAST((@pInputSecs%3600.0)/60.0 as float),0) as varchar(MAX)),2),'0:00')
END

RETURN @HHMM


END

--To Get Results

SELECT dbo.ufn_GetHHMM(DurationinSeconds) --Assume you have column name 'DurationinSeconds'

No comments:

Post a Comment