Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    106

    Unanswered: converting decimal to time

    how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

    28.5000

    but I want to show it in this format:

    28:30:00

    where 28 = hours, 30 = minutes, 00 = seconds

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a presentation issue. It should be handled at the client, not the server. Dealing with formatting inside the database is just a recipe for problems later.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What Pat said

    Code:
    DECLARE @hours decimal(15,4)
    SELECT @hours = 28.5
    SELECT    RIGHT('00' + CONVERT(varchar(2),FLOOR(@hours)),2)
    	+':'
    	+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hours-FLOOR(@hours))*60))),2)
    	+':'
    	+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hours-FLOOR(@hours))*60)-FLOOR(((@hours-FLOOR(@hours))*60)))*60),2)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Displaying it as 28:30:00 is a presentation issue, but converting it to a valid datetime format falls within the scope of the database server:
    Code:
    declare @Hours decimal (6, 4)
    set	@Hours = 28.5
    select	dateadd(minute, @Hours * 60, 0)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was gonna give them that, but I realized it wasn't what they asked for...

    Hours of what BTW....sounds like derived data gotta be careful with that
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •