Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: Help on conversion

    We have a database where all of the times have been stored as integers equal to the number of seconds from midnight. For several reports I need to convert these back into Times.

    Example

    INT TimeValue
    27000 7:30 A.M.
    59400 4:30 P.M.

    I've looked at the way the vendor is doing this for some reports and it just looks goofy and way too complicated.

    Step 1 determine the hour: 27000/3600 = 7 (INT)
    Step 2 determine the minutes: 27000/60 = 450 (INT)
    Step 3 determine the remainder or actual minutes 450 - (7*60) = 30
    Step 4 Contatanate results from steps 1 and 3 together 7 + ':' + 30

    There has to be a better way!

    Thanks,

    Brent

  2. #2
    Join Date
    Dec 2002
    Posts
    63
    If I were you, I would create a function that would do the math, and then call it when you needed the result.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, create a function, but create a simpler one. You ought to be able to use the DateAdd function, which can take seconds as a parameter. You can add the seconds to any integer date (use date zero, which is something like 1/1/1900) and then you can display it as whatever time format you want.

    Sorry if this is a little fuzzy. I don't have access to SQL syntax help at the moment.

    blindman

  4. #4
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Thanks for the help, I was able to get this to work with the following code:

    1. Converting time:

    Convert(varchar(10),Dateadd("s",[Fieldname],0),8) As outputname
    from tblname

    2. Figuring out the elapsed time between two values:

    Convert(varchar(10),DateAdd("s",Datediff("s",DateA dd("s",[Fieldname1],0),DateAdd("s",[Fieldname2,0)),0),8)
    from [tblname]

    Brent


    Originally posted by blindman
    Yes, create a function, but create a simpler one. You ought to be able to use the DateAdd function, which can take seconds as a parameter. You can add the seconds to any integer date (use date zero, which is something like 1/1/1900) and then you can display it as whatever time format you want.

    Sorry if this is a little fuzzy. I don't have access to SQL syntax help at the moment.

    blindman

Posting Permissions

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