calculating time? 1

[Ryon]

I'm currently using access as a front end and as a back end. My problem is that access doesnt have any functionality to calculate time

for example: 2:20
+2:50
=4:70
not 5:10 like its suppose to be. I have gotten around that using VBA but, it has become quite a hassle.

I was wondering if SQL server has any built in functionality to calculate time?

Thanks ahead of time
Ryan

 

[nigelrivett]

what sort of a field are you hoolding the times in?
If in datetime fields

fld1 + fld2
will guive the correct result.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Ryon]

yes short time, but it doesnt calculate. It should, but it doesn't.

Im using access, not SQL. Are you saying that SQL will calculate it correctly?

Even in a access SQL query when you add 2 times together that have short time property value, it sums it to a number format that comes out like 0.00347222222222222.

the only way I have found around this is
bring the query to a form, delete the sum field, create a textbox, set the property to short time and then reassociate it to the sum field

I guess Im wondering if the SQL sever object has different field properties for time, therefore calculating correctly.


thanks for your help,
Ryan

 

[SQLBill]

To add to Nigel's post.

YES. You must create your column(s) as DATETIME or SMALLDATETIME datatype. Then DATEDIFF function will do what you want.


-SQLBill

 

[nigelrivett]

If I define 2 datetime fields in access and add them together I get 5:10 not 4:70.

What are you actually doing?


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Ryon]

yes it does work when your creating a query and a new field, say total:sum([time]+[time2]

my problem is when you try and total the query using sum function for example

time time2
4:55 4:15
3:25 3:15

SUM SUM

 

[SQLSister]

If you are talking about adding together hours and minutes to get a total amount of time spent on this, I suggest you store them in a numeric field as minutes and then you can use sunm to get thet toal number of minutes. You can tehn use a function to convert to hours and minutes if need be. Time in a date time field refers to an actual time (2:00 pm) not a number of hours and minutes (2 hours and 0 minutes).

 

[Ryon]

yes, thats exactly what Im doing. I was just wondering if using sql as a backend would give me any help in doing so.

thanks for your help

 

[nigelrivett]

You can't sum datetimes in sql server but a datetime is a decimal number so you can
select convert(datetime,sum(convert(float,dtefld)))


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.