Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How can i convert numeric value in to time (HH:MM).
Posted
Comments
ArunRajendra 20-Feb-14 3:16am    
Give some example for the way you expect.
chetna2810 20-Feb-14 3:19am    
i have a record in the table like 635 in numeric. i want to convert in into 06 hours and 35 min.
or 18 hr 35 min
chetna2810 20-Feb-14 3:46am    
Thnx sir..
chetna2810 20-Feb-14 3:57am    
Sir i want to substract the resultant value from current time..how this can be done
chetna2810 21-Feb-14 2:31am    
@george4986 Thnx for the reply sir..
I m writing this script
DECLARE @LateHours TIME;
DECLARE @Lintime DATETIME;
DECLARE @LintimeM NUMERIC(4,0);

set @Lintime='1900/01/01 10:12:00';
set @LintimeM=1015;
--set @Lintime='1900/01/01 20:12:00';
--set @LintimeM=1830;
SELECT @LateHours = convert(TIME,dateadd(MINUTE,-((CAST(@LintimeM/100 AS INT)*60)+RIGHT(@LintimeM,2)),@Lintime),24);
SELECT @Lintime AS 'Lintime';
SELECT @LintimeM AS 'LintimeM';
SELECT @LateHours AS 'LateHours';

getting result 23:57:00.0000000 when taking time 1015 but ans should be 00:03:57.00000 this.. plz check this..

You can write sql script like below
SQL
declare @time int
set @time = 635

select cast((@time / 60) as varchar(2)) + ' hr : ' + cast((@time % 60) as varchar(2))+' min'
 
Share this answer
 
SQL doesn't have a data type for timespans - so there is nothing you can return as a single item other than VARCHAR.
SQL
DECLARE @TIME INT
SET @TIME = 635
SELECT CONVERT(VARCHAR(10), @TIME / 100) + ' hours, ' + CONVERT(VARCHAR(10), @TIME % 100) + ' minutes'


Or you could return it as two columns.
SQL
DECLARE @TIME INT
SET @TIME = 635
SELECT @TIME / 100 AS [Hours], @TIME % 100 AS [Minutes]
 
Share this answer
 
"substract the resultant value from current time"
check this code

SQL
DECLARE @TIME numeric
SET @TIME = 635
select CONVERT(VARCHAR(5),dateadd(MI, -((CAST( @TIME/100 as int)*60)+ RIGHT(@TIME,2)), getdate()),24)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900