# CAST Function not working • ### Question

• I am trying to divide two number than multiply that result by 365. I have tried at least 10 differenct combinations of cast and I still get results with 15 decimal places. I am using the code:

,CASE
WHEN DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1) <= 366 THEN OP.ExtendedAmount

WHEN DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1) > 366 THEN CAST(CAST(OP.ExtendedAmount as   decimal(7,2))/CAST(DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1) as decimal(4,0))*365)
ELSE 0
END AS 'Renewal_ARR'

The first case staement works great.

The second statement is the one giving me the trouble.

Tuesday, December 1, 2020 4:12 PM

• Using round with decimal has got me as close as I think I can get.

WHEN DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1) > 366 THEN ROUND((CAST(OP.ExtendedAmount as decimal(8,2))/CAST(DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1) as float)*365),2)

gets me a result of 21583.2 when I would like 21583.20 but I think I am close enough..

• Marked as answer by Tuesday, December 1, 2020 5:09 PM
Tuesday, December 1, 2020 5:09 PM

### All replies

• I have changed to using convert and I get the 2 decimal places I want.

WHEN DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1) > 366 THEN CONVERT(int,OP.ExtendedAmount)/CONVERT(int,DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1))*365

• Marked as answer by Tuesday, December 1, 2020 4:26 PM
• Unmarked as answer by Tuesday, December 1, 2020 4:36 PM
Tuesday, December 1, 2020 4:25 PM
• This didn't work I got the two decimal places but they always come up .00
Tuesday, December 1, 2020 4:37 PM
• I have eliminated using int, bigint and numeric, these casue the accuracy of the result to off by to much. So I have gone back to decimal(8,2).

I have tried using the round function but that does not change the amount of decimals to the right of the number. I am getting 21583.20000000000 instead of 21583.20.

Tuesday, December 1, 2020 5:00 PM
• Using round with decimal has got me as close as I think I can get.

WHEN DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1) > 366 THEN ROUND((CAST(OP.ExtendedAmount as decimal(8,2))/CAST(DATEDIFF(day,det.ordd_mn_start_date,det.ordd_mn_end_date+1) as float)*365),2)

gets me a result of 21583.2 when I would like 21583.20 but I think I am close enough..

• Marked as answer by Tuesday, December 1, 2020 5:09 PM
Tuesday, December 1, 2020 5:09 PM