Subtracting two dates using PostgreSQL

Question

I am trying to subtract 2 dates from each other but it seems that it is not subtracting properly and i am not sure what i am doing wrong here. I am using case statement to flag as 1 if the difference between the dates are less than 90 days else flag it as 0. But it is always flagging as 1 even if the difference between the dates are greater than 90 days. I am PostgreSQL here and here is my case statement:

CASE WHEN EXTRACT(DAY FROM CAST(SVS_DT AS DATE) - CAST(DSCH_TS AS DATE)) <90 
      THEN 1 ELSE 0 END AS FU90

example of the dates are here:

SVS_DT                         DSCH_TS
2013-03-22 00:00:00            2010-05-06 00:00:00

it is suppose to flag as 0 in this case but it is flagging as 1 because the difference between these 2 dates are greater than 90 days.

Answer 1

extract of a day returns the day element of a date. Since days are always between 1 and 31, the maximum difference is 30, and cannot be larger than 90.

Subtracting dates returns the difference in days, as an integer. So you need only drop the extract calls:

CASE WHEN (CAST(SVS_DT AS DATE) - CAST(DSCH_TS AS DATE)) < 90 THEN 1 
                                                              ELSE 0 
     END AS FU90

Answer 2

you can use below one:

CASE WHEN (EXTRACT(EPOCH FROM (DATE_COLUMN_2 - DATE_COLUMN_1)) < (90*24*60*60) 
    THEN 1 ELSE 0 END AS FU90

here it returns the deference in seconds so you need to convert 90 days into seconds