CODEWITHSUNDEEP
sqlpostgresqldate
moe
moe

Reputation: 5249

Subtracting two dates using PostgreSQL

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.

Upvotes: 6

Views: 6523

Answers (4)

Rahul Ghadge
Rahul Ghadge

Reputation: 131

We can also do it without using any EXTRACT or CAST keyword like:

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

Upvotes: 0

Tim Duncklee
Tim Duncklee

Reputation: 1420

A slightly shorter CAST version.

SELECT CASE WHEN SVS_DT::DATE - DSCH_TS::DATE < 90
    THEN 1
    ELSE 0
END 
AS FU90

Upvotes: 0

Mureinik
Mureinik

Reputation: 312259

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

Upvotes: 4

Prasad Khode
Prasad Khode

Reputation: 6739

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

Upvotes: 1

Related Questions