I have a select query that has DURATION
column to calculate number of Minutes . I want to convert those minutes to hh:mm
format.
Duration has values like 60, 120,150
For example:
60 becomes 01:00 hours
120 becomes 02:00 hours
150 becomes 02:30 hours
Also, this is how I retrieve DURATION (Minutes
)
DATEDIFF(minute, FirstDate,LastDate) as 'Duration (Minutes)'
This question is related to
sql-server
tsql
sql-server-2008
type-conversion
datediff
declare function dbo.minutes2hours (
@minutes int
)
RETURNS varchar(10)
as
begin
return format(dateadd(minute,@minutes,'00:00:00'), N'HH\:mm','FR-fr')
end
I would do the following (copy-paste the whole stuff below into immediate window / query window and execute)
DECLARE @foo int
DECLARE @unclefoo smalldatetime
SET @foo = DATEDIFF(minute, CAST('2013.01.01 00:00:00' AS datetime),CAST('2013.01.01 00:03:59' AS datetime)) -- AS 'Duration (Minutes)'
SET @unclefoo = DATEADD(minute, @foo, '2000.01.01')
SELECT CAST(@unclefoo AS time)
@foo
stores the value you generate in your question. The "trick" comes by then:
we create a smalldatetime
variable (in my case it's yyyy.mm.dd
format) and increment it with your int
value, then display (or store if you want) the time
part only.
In case someone is interested in getting results as 60 becomes 01:00 hours, 120 becomes 02:00 hours, 150 becomes 02:30 hours, this function might help:
create FUNCTION [dbo].[MinutesToHHMM]
(
@minutes int
)
RETURNS varchar(30)
AS
BEGIN
declare @h int
set @h= @minutes / 60
declare @mins varchar(2)
set @mins= iif(@minutes%60<10,concat('0',cast((@minutes % 60) as varchar(2))),cast((@minutes % 60) as varchar(2)))
return iif(@h <10, concat('0', cast(@h as varchar(5)),':',@mins)
,concat(cast(@h as varchar(5)),':',@mins))
end
DECLARE @Duration int
SET @Duration= 12540 /* for example big hour amount in minutes -> 209h */
SELECT CAST( CAST((@Duration) AS int) / 60 AS varchar) + ':' + right('0' + CAST(CAST((@Duration) AS int) % 60 AS varchar(2)),2)
/* you will get hours and minutes divided by : */
Thanks to A Ghazal, just what I needed. Here's a slightly cleaned up version of his(her) answer:
create FUNCTION [dbo].[fnMinutesToDuration]
(
@minutes int
)
RETURNS nvarchar(30)
-- Based on http://stackoverflow.com/questions/17733616/how-to-convert-number-of-minutes-to-hhmm-format-in-tsql
AS
BEGIN
return rtrim(isnull(cast(nullif((@minutes / 60)
, 0
) as varchar
) + 'h '
,''
)
+ isnull(CAST(nullif((@minutes % 60)
,0
) AS VARCHAR(2)
) + 'm'
,''
)
)
end
For those who need convert minutes to time with more than 24h format:
DECLARE @minutes int = 7830
SELECT CAST(@minutes / 60 AS VARCHAR(8)) + ':' + FORMAT(@minutes % 60, 'D2') AS [Time]
Result:
130:30
This seems to work for me:
SELECT FORMAT(@mins / 60 * 100 + @mins % 60, '#:0#')
I'm not sure these are the best options but they'll definitely get the job done:
declare @durations table
(
Duration int
)
Insert into @durations(Duration)
values(60),(80),(90),(150),(180),(1000)
--Option 1 - Manually concatenate the values together
select right('0' + convert(varchar,Duration / 60),2) + ':' + right('0' + convert(varchar,Duration % 60),2)
from @Durations
--Option 2 - Make use of the time variable available since SQL Server 2008
select left(convert(time,DATEADD(minute,Duration,0)),5)
from @durations
GO
How to get the First and Last Record time different in sql server....
....
Select EmployeeId,EmployeeName,AttendenceDate,MIN(Intime) as Intime ,MAX(OutTime) as OutTime,
DATEDIFF(MINUTE, MIN(Intime), MAX(OutTime)) as TotalWorkingHours
FROM ViewAttendenceReport WHERE AttendenceDate >='1/20/2020 12:00:00 AM' AND AttendenceDate <='1/20/2020 23:59:59 PM'
GROUP BY EmployeeId,EmployeeName,AttendenceDate;
This function is to convert duration in minutes to readable hours and minutes format. i.e 2h30m. It eliminates the hours if the duration is less than one hour, and shows only the hours if the duration in hours with no extra minutes.
CREATE FUNCTION [dbo].[MinutesToDuration]
(
@minutes int
)
RETURNS nvarchar(30)
AS
BEGIN
declare @hours nvarchar(20)
SET @hours =
CASE WHEN @minutes >= 60 THEN
(SELECT CAST((@minutes / 60) AS VARCHAR(2)) + 'h' +
CASE WHEN (@minutes % 60) > 0 THEN
CAST((@minutes % 60) AS VARCHAR(2)) + 'm'
ELSE
''
END)
ELSE
CAST((@minutes % 60) AS VARCHAR(2)) + 'm'
END
return @hours
END
To use this function :
SELECT dbo.MinutesToDuration(23)
Results: 23m
SELECT dbo.MinutesToDuration(120)
Results: 2h
SELECT dbo.MinutesToDuration(147)
Results: 2h27m
Hope this helps!
select convert(varchar(5),dateadd(mi,DATEDIFF(minute, FirstDate,LastDate),'00:00'),114)
Source: Stackoverflow.com