1. Registered User
Join Date
Sep 2002
Location
Mancos, CO
Posts
73

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. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
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:

from tblname

2. Figuring out the elapsed time between two values:

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
•