locked
CAST Function not working RRS feed

  • 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

Answers

  • 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 sqlhelp555 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 sqlhelp555 Tuesday, December 1, 2020 4:26 PM
    • Unmarked as answer by sqlhelp555 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 sqlhelp555 Tuesday, December 1, 2020 5:09 PM
    Tuesday, December 1, 2020 5:09 PM