I don’t know if this is possible but I am trying to return contracted hours from our rota system using the select statement below to return just the Hours and minutes from a date and time column.
However, it only returns values accurately up to 23:59, if I key in contracted hours of 24 or more it doesn’t return accurately i.e. 24 will return 0, 25 returns 1, 26 returns 2, 27 returns 3 and so on.
Most of our staff have contracted hours of 39, so in the Contracted hours filed in our rota system I key in 39:00 but it comes out as 15 but of course I need it to show as 39:00. I guess the issue is to do with date and time formatting using a 24 hour clock but I can’t find out how to get it to return the value entered into the rota system and ignore the 24 hours rule.
Any help with this will be very much appreciated
Thank you
Jason
This is the select statement I am using at the moment
COALESCE(DATEPART(hour, tblCarerContract.ContractHours), 0) + (COALESCE(DATEPART(minute, tblCarerContract.ContractHours), 0) / 60.0) as ContractHours
The max value of date is 23:59:59. If you need to record value longer then 23:59:59, i think you have to put this value in number column and have to do the convertions in you code.