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,

BrentIf I were you, I would create a function that would do the math, and then call it when you needed the result.|||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|||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",DateAdd("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