CODEWITHSUNDEEP
sqlsql-servert-sqlcasting
Kyle Rickaby
Kyle Rickaby

Reputation: 117

SQL Casting with Decimals

Lets say I get 8048 seconds for my SQL code:

SUM(DATEDIFF(s, '8/25/2015', '8/26/2015')) as 'Seconds'

How can I get the same answer for Minutes and hours... but have them be a decimal? This is for hours:

 Cast(((SUM(DATEDIFF(s, '8/25/2015', '8/26/2015'))/60.00)/60.00) as decimal(8,2)) as 'Hours'

But I keep getting 2.00 hours.... when it should be 2.34 hours?

Upvotes: 0

Views: 112

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

DATEDIFF returns an interval. CAST it as a float or decimal to get a decimal result from your math formula:

 Cast(((SUM(CAST(DATEDIFF(s, '8/25/2015', '8/26/2015') AS decimal(8,2))/60.00)/60.00) as decimal(8,2)) as 'Hours'

Upvotes: 1

Sam CD
Sam CD

Reputation: 2097

Try relocating the CAST statement to inside of the SUM():

CAST((SUM(CAST(DATEDIFF(s, [date1], [date2]) AS Decimal(20,2))/60.00)/60.00)) AS Decimal(20,2)) as 'Hours'

The way you have it set up now, the value is probably rounded before you even cast it

Upvotes: 0

Related Questions