How can I resolve "Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query." error?

Question

I have 2 date fields of dateTime data type each.

One is called PhaseEnd and the other is PhaseStart.

We would like to substract PhaseStart from PhaseEnd to get the number of months between the two.

When I run the following query:

SELECT (CASE WHEN PhaseEnd IS NOT NULL THEN round((PhaseEnd - PhaseStart)/30,1)
       ELSE round((getdate() - PhaseStart)/30,1) END) Months from tblT_PHASES

I get Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Any idea how to fix this?

Answer 1

use datediff

select
    case
        when PhaseEnd is not null then
            round(datediff(day, PhaseStart, PhaseEnd)/30,1)
        else
            round(datediff(day, PhaseStart, getdate())/30,1)
    end as Months
from tblT_PHASES

or

select
   round(
       datediff(day, PhaseStart, isnull(PhaseEnd, getdate())) / 30
   , 1) as Months
from tblT_PHASES