Number to date conversion (hour:Minutes:Sec)

• Jawad Ahmed Siddiqui

  Old Hand

  Points: 301

  More actions

  March 30, 2006 at 10:08 pm

  #167544

  Hi

  I have a Column say (Tab1.Col_A number (20,5)) store seconds.

  I want result in Hours:Minutes:Seconds format

  Please advise how to get above outcome.

  Note:- At the moment I do Col_A/60 for minutes and Col_A/(60X60) for hours but it is not accurate it some time give 12Hours & 80Minutes.

  Wishes

  Jawad

   

• ajay_k73

  SSC Veteran

  Points: 217

  More actions

  March 31, 2006 at 1:55 am

  #630026

  You need to use function to get field in hh:mm format

  function D_Time (@S_Time as int)

  as

  begin

  DECLARE @STR INT

  DECLARE @QRY VARCHAR(10)

  SET @STR=@S_Time

  SET @QRY=CAST(@STR/3600 AS VARCHAR(2))+':'

  SET @STR=@STR%3600

  SET @QRY=@QRY+CAST(@STR/60 AS VARCHAR(2))+':'+CAST(@STR%60 AS VARCHAR(2))

  return @QRY

  end

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  March 31, 2006 at 2:59 am

  #630029

  This works for periods less than 1 day.

  declare @secs float

  set @secs = 15000

  select convert(char(8), cast(@secs / 86400 as datetime), 8)

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Jawad Ahmed Siddiqui

  Old Hand

  Points: 301

  More actions

  March 31, 2006 at 3:47 am

  #630031

  thanks

  Jawad