Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dzabor
Posting Yak Master
138 Posts |
 Posted - 2008-01-27 : 01:28:42
|
| I need to create a calculation between to number fields two fields.Field 1 - A number based off a calculation of time (the difference between hours & minutes from two fields)and returning hours and minutes - 7.30Field 2 - A number based stored in a table alreads returning the number of hours and minutes - 7.50These two numbers mean the same thing, but when sutracting one from teh other it returns .20 and shoudl return 0.How can I calculate either field one to return 7.50 or field 3 to return 7.30?DZ |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-01-27 : 03:07:35
|
quote:
These two numbers mean the same thing
What do you mean ? How does 7 hours 30 minutes same as 7 hours 50 minutes ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2008-01-27 : 08:48:32
|
| 7.5 is 7 1/2 hours. When entering time/hours into the database it shows the totalled hours in the increments 7, 7.25, 7.5, 7.75. This is not based on any calculation, it is just a number field already biult into the program. The application manula says to enter it this way because of other calculations set up.For the other field I had to create a calculation using 2 date and time field to find out how many hours someone is supposed to work. CONVERT(Varchar, 0 - (DATEDIFF(hh, starttime, endtime) / 24 * 24 - DATEDIFF(hh, starttime, endtime))) + '.' + CONVERT(Varchar, 0 - (DATEDIFF(minute, starttime, endtime) / 60 * 60 - DATEDIFF(minute, starttime, endtime))) AS HrsMins. This returns in actual hours and minutes, 7, 7.3, 7.50, etc.I need these two fields to use the same format so I can run a calculation on them. This calculation will sustract the number of hours worked from the number of hours someone is supposed to work. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-01-27 : 09:45:46
|
depends on what you need to do with the value. You can easily convert from one representation to another.7.50 to 7.30select convert(int, 7.5) + ((7.5 * 60) % 60) / 1007.30 to 7.50select convert(int, 7.5) + (7.3 * 100) % 100 / 60.0or just convert both to minutes and perform your calculation in minutes.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2008-01-27 : 10:34:09
|
| thanks - I got it to work for converting quarter hours to minutes - 7.5. to 7.30. This required little scripting because it is a field that has not been converted. IO am struggling how to convert the other field (below) from minutes to quarter hours,. Since this will be the format in all other reports coming out of teh application it would make more sense to keep consistant. The problem is the field i need calculatged was credated form the calculation below and I cannot figure out how to add it to the current formula. This returns 7.3. Can you help me add the conversion to this formula?The formula is returnign the 7.3 based on the starttime and endtime fields in a work table.CONVERT(Varchar, 0 - (DATEDIFF(hh, starttime, endtime) / 24 * 24 - DATEDIFF(hh, starttime, endtime))) + '.' + CONVERT(Varchar, 0 - (DATEDIFF(minute, starttime, endtime) / 60 * 60 - DATEDIFF(minute, starttime, endtime))) AS HrsMins |
|
|
|
|
|
|
|
|
Convert Number to minutes