September 5, 2018 at 7:56 am
Hi I am using the below code to convert decimal times into hh:mm:ss
e.g.
0.18 minutes = 10.8 seconds rounder up to 11 seconds.
CAST(DATEADD(SECOND, Time_Talk_Total * 60, 0) AS time(0)) as [Time_Talk_Total_HHMMSS],
This works but always seems to round down in this case to 00:00:10
can anyone assist in rounding up or down based on the decimal being <> 0.5 please?
Cheers
Dave
September 5, 2018 at 8:18 am
david_h_edmonds - Wednesday, September 5, 2018 7:56 AMHi I am using the below code to convert decimal times into hh:mm:ss
e.g.0.18 minutes = 10.8 seconds rounder up to 11 seconds.
CAST(DATEADD(SECOND, Time_Talk_Total * 60, 0) AS time(0)) as [Time_Talk_Total_HHMMSS],
This works but always seems to round down in this case to 00:00:10
can anyone assist in rounding up or down based on the decimal being <> 0.5 please?
Cheers
Dave
To round up or down based on the decimal being <> 0.5CAST(DATEADD(SECOND, Time_Talk_Total * 60 + 0.5, 0) AS time(0)) as [Time_Talk_Total_HHMMSS]
To round upCAST(DATEADD(SECOND, CEILING(Time_Talk_Total * 60), 0) AS time(0)) as [Time_Talk_Total_HHMMSS]
September 6, 2018 at 5:30 am
As they say, read that manual.
When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.
From To Behavior numeric numeric Round numeric int Truncate numeric money Round money int Round money numeric Round float int Truncate float numeric Round Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.
float datetime Round datetime int Round For example, the values 10.6496 and -10.6496 may be truncated or rounded during conversion to int or numeric types:
The implicit conversion of decimal value Time_Talk_Total * 60 to int causes truncation of the decimal part of the number.
So, your solution would be - add ROUND function:
CAST(DATEADD(SECOND, ROUND(Time_Talk_Total * 60, 0), 0) AS time(0)) as [Time_Talk_Total_HHMMSS]
_____________
Code for TallyGenerator
September 11, 2018 at 1:02 pm
Sergiy - Thursday, September 6, 2018 5:30 AMAs they say, read that manual.When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.
From To Behavior numeric numeric Round numeric int Truncate numeric money Round money int Round money numeric Round float int Truncate float numeric Round Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.
float datetime Round datetime int Round For example, the values 10.6496 and -10.6496 may be truncated or rounded during conversion to int or numeric types:
The implicit conversion of decimal value Time_Talk_Total * 60 to int causes truncation of the decimal part of the number.
So, your solution would be - add ROUND function:
CAST(DATEADD(SECOND, ROUND(Time_Talk_Total * 60, 0), 0) AS time(0)) as [Time_Talk_Total_HHMMSS]
Change 60 to 60. and the implicit conversion goes away. I'd still recommend using ROUND on the result.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2018 at 4:10 pm
Conversion to integer happens not on multiplication but on applying the result to the function parameter.
60 is implicitly converted to decimal anyway.
_____________
Code for TallyGenerator
September 12, 2018 at 10:12 am
Sergiy - Tuesday, September 11, 2018 4:10 PMConversion to integer happens not on multiplication but on applying the result to the function parameter.60 is implicitly converted to decimal anyway.
True, but I avoid implicit conversions as they can mess up the optimizer's estimates severely, and I try really hard NOT to mess up the optimizer. It already has the most challenging job, and there's no point in making it work harder for no benefit.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply