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 |
|
crazyme
Starting Member
11 Posts |
 Posted - 2006-10-28 : 06:14:57
|
| My requirement is i need to find the hours worked by an employee on a holiday in case if he had worked more than 7.5 hours he is eligible for an PTO on a working day, if it is less than 7.5 he is eligible for a half day PTO.When i used datediff for hours it is giving me an integer value.select (DATEDIFF(hh,'2006-10-28 08:15:00.270', '2006-10-28 15:45:45.270')). I want the result to be of one place decimal viz, 7.5 hours. How do i go about?Knowledge grows when shared... |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-28 : 06:39:16
|
| select (DATEDIFF(minute,'2006-10-28 08:15:00.270', '2006-10-28 15:45:45.270')) / 60.0Kristen |
 |
|
|
crazyme
Starting Member
11 Posts |
Posted - 2006-10-28 : 07:07:08
|
| Thanks that worked like a chum...and one clarification is.select (DATEDIFF(minute,'2006-10-28 08:15:00.270', '2006-10-28 15:45:45.270')) / 60.0wat difference does it make when it is...select (DATEDIFF(minute,'2006-10-28 08:15', '2006-10-28 15:45')) / 60We were getting 7...and not 7.5 as desired(which we got from your query).Knowledge grows when shared... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-28 : 07:09:23
|
DATEDIFF(minute,'2006-10-28 08:15:00.270', '2006-10-28 15:45:45.270') will give the time different in minutedivide by 60.0 will give in hour with decimals
KH |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-28 : 07:59:14
|
| If you refer to the division with 60.0 and the division with 60, the difference is that with decimal point, the result is also decimal. Without decimal point, the result is integer division. That can lead to unpredicted results such as18 / 5 = 318 / 5.0 = 3.6Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-28 : 14:19:55
|
| Yeah, I probably should have explained that. It would probably be better to explicitly CAST the DATEDIFF to a Decimal, rather than relying on the side effect of using a constant with an explicit decimal (60.0 in this case) as someone not familiar with the reason might easy not realise the significance when re-reading the code in 5 years time!Kristen |
|
|
|
|
|
|
|
|
DateDiff