Add Hour question

[QueenP]

Hi,
I have to lots of hours but I can't figure out a way of doing it properly.

The values I need are stored like 1/1/1900 01:35:00, but everytime I add this column, it also adds the date, so the final result is something like 27/1/1900 15:54:32.

Is there a way of just adding the time and leaving the date out?

 

[rac2]

Can you redefine your table to store the duration as a number instead of as a time-of-day 0n Jan 1, 1900?

What database are you using?

The duration can be calculated as the difference between the datetime value you have and the datetime value for '01/01/1900 00:00:00'; minutes would be a handy unit for the resulting number. In MS SQL this would be

DATEDIFF(mi, '01/01/1900 00:00:00', hours_worked)

In Access this would be

DateDiff("n", #01/01/1900 00:00:00#, [hours_worked])

 

[robi2]

dont understand your question....
in your examples you also added date and hour and in the result you have date and hour...

if you only want to add time, then just use the time() function.

 

[QueenP]

What I have is a table in SQL Server that stores the free time between one call and another for each user (it's a call centre program).

I needed to add all of the free time from all of the users, but when I try to add 03:05:00 and 02:15:02 which would be 05:20:02, I get a wrong result because of the day.

I thought about transforming all the time in minutes and then adding those, but that's as far I as went.

 

[robi2]

try using the timevalue() function, this will return the time from the string.

time1 = "02/05/03 03:05:00"
time2 = "08/05/03 05:45:00"


totalTime = timevalue(time1) + timevalue(time2)

totalTime will now contain 08:50:00.

 

[QueenP]

Hi robi2,

I tried using the timevalue() function but it didn't work.

If it'll help, I have these values:
1900-01-01 00:27:00
1900-01-01 03:28:00
1900-01-01 04:16:00
1900-01-01 03:00:00
1900-01-01 03:51:00
1900-01-01 05:54:00
And wanted to loop through them to find 20:56:00 as a result, but what I got instead was 31/12/1899 00:48:00.

In my code I have somthing like the following:

Do While Not rst3.EOF
tot = timevalue(rst3("toTotal&quot) + tot
rst3.MoveNext
Loop
rst3.Close

Thanks.

 

[pgferro]

MyHours=0
MyMinutes=0
MySeconds=0
Do While Not rst3.EOF
  MyHours = MyHours + DatePart("h", rst3("toTotal"))
  MyMinutes = MyMinutes + DatePart("n", rst3("toTotal"))
  MySeconds = MySeconds + DatePart("s", rst3("toTotal"))
  rst3.MoveNext
Loop
rst3.Close

You will have 3 variables with the total of hours, minutes, seconds. You can then do whatever you wish...

Hope it helps

 

[robi2]

what kind of dates are thoes?!

 

[QueenP]

It looks like the way that pgferro did is working.

robi2, they're smalldatetime.