2

When I use a DateDiff in a SELECT statement in SQL Server, I get an int value back:

select DATEDIFF(day,'01/01/2011','15/05/2011') 
from Table1

Result is : 134

select DATEDIFF(day,'01/01/2011','15/05/2011')/15 
from Table1

Result is : 8

select cast(DATEDIFF(day,'01/01/2011','15/05/2011') / 15 as Decimal(9,2)) 
from Table1

Result is : 8.00

But in reality the result is : 8.9333333

How can I change the result from int to Double?

I want to get the result 8.9333333 - how can I do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fadel lion
  • 23
  • 4

3 Answers3

3

The problem is that you're using CAST after dividing. If you perform the division after the CAST, you'll get the answer you want:

select 
   cast(DATEDIFF(day,'01/01/2011','15/05/2011') as Decimal(9,2))/15 
from Table1
mjsqu
  • 5,151
  • 1
  • 17
  • 21
3

You need to cast your inputs into the division as decimal first:

select cast(DATEDIFF(day,'2011-01-01','2011-05-15') as Decimal(9,2))/15.0

Otherwise you are dividing an integer by an integer - you'll get an integer out. If you then cast this to a decimal after the division, you are going to get 8.0.

Paddy
  • 33,309
  • 15
  • 79
  • 114
1
SELECT CAST(DATEDIFF(day,'01/01/2011','15/05/2011') AS Decimal(9,2))/15 
FROM Table1
Matt
  • 14,906
  • 27
  • 99
  • 149