Rounding Down of Values - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rounding Down of Values

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-01-26 : 04:04:38

I have this query below which is taking fields which are mins to hours. My problem is that it just rounds the numbers down, how is it posible to show it to 2 decimal places?


SELECT jez.MISTData.THE_YEAR, jez.MISTData.THE_MONTH, jez.MISTData.MIS_HR_EMPLOYEES_ID, jez.MISTData.FORENAME, jez.MISTData.SURNAME, 
jez.MISTData.FTE, jez.MISTData.CTR_HRS, jez.MISTData.PRIMARY_ASSIGNMENT, jez.MISTData.[BAND], jez.MISTData.CARE_GROUP, jez.MISTData.CARE_GROUP_TEAM, 
jez.MISTData.AVG_SALARY, jez.MISTData.CONTACT_COST, jez.MISTData.CONTACT_INCOME, jez.MISTData.TIME_PE, jez.MISTData.TIME_NPE, jez.MISTData.TIME_OTHER, 
jez.MISTData.TIME_TRAVEL, jez.MISTData.TIME_PROFESSIONAL, jez.MISTData.TIME_NON_PROFESSIONAL, jez.MISTData.TIME_HOLIDAY, jez.MISTData.TIME_ABSENT, 
jez.MISTData.TIME_OVER, jez.MISTData.PERCENT_TIME_PE, jez.MISTData.PERCENT_TIME_NPE, jez.MISTData.PERCENT_TIME_OTHER, jez.MISTData.PERCENT_TIME_TRAVEL, 
jez.MISTData.PERCENT_TIME_PROFESSIONAL, jez.MISTData.PERCENT_TIME_NON_PROFESSIONAL, jez.MISTData.PERCENT_TIME_HOLIDAY, jez.MISTData.PERCENT_TIME_ABSENT, 
jez.MISTData.PERCENT_TIME_OVER, [Forename] + ' ' + [Surname] AS [Name], [time_pe]/60 AS TimePEHours, [time_npe]/60 AS TimeNPEHours, [TIME_OTHER]/60 AS TimeOthersHours, 
[TIME_TRAVEL]/60 AS TravelTimeHours, [TIME_PROFESSIONAL]/60 AS TimeProfHours, [TIME_NON_PROFESSIONAL]/60 AS TimeNonProfHours, [TIME_HOLIDAY]/60 AS TimeHolsHours, 
[TIME_ABSENT]/60 AS TimeAbsentHours, [TIME_OVER]/60 AS TimeOverHours
FROM jez.MISTData

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-26 : 04:53:04

Don't use integer division.
Divide by 60.0E to use float division.

CAST(TIME_OVER / 60.0E AS DECIMAL(18, 2))

E 12°55'05.63"
N 56°04'39.26"