postgresql - Problem with data type casting and calculating in postgresql

Question

I have the code look like this:

CASE
           WHEN EXTRACT(YEAR
                        FROM REPORTING_DATE) >= EXTRACT(YEAR
                                                              FROM ('2018/11/09 14:11:31' :: DATE)) THEN EXTRACT(YEAR
                                                                                                    FROM REPORTING_DATE)
           WHEN EXTRACT(YEAR
                        FROM ('2018/11/09 14:11:31' :: DATE) - EXTRACT(YEAR
                                                          FROM REPORTING_DATE)) >= 2 THEN EXTRACT(YEAR
                                                                                                        FROM ('2018/11/09 14:11:31' :: DATE))
           WHEN EXTRACT(YEAR
                        FROM REPORTING_DATE) < EXTRACT(YEAR
                                                             FROM ('2018/11/09 14:11:31' :: DATE))
                AND EXTRACT(YEAR
                            FROM REPORTING_DATE) - EXTRACT(YEAR
                                                                 FROM ('2018/11/09 14:11:31' :: DATE)) < 2
                AND EXTRACT(MONTH
                            FROM ('2018/11/09 14:11:31' :: DATE)) < 6 THEN EXTRACT(YEAR
                                                                      FROM REPORTING_DATE)
           ELSE EXTRACT(YEAR
                        FROM ('2018/11/09 14:11:31' :: DATE))

After proceeding, it shows message:

Operator does not exist: date - double precision

At the location:

WHEN EXTRACT(YEAR
                        FROM ('2018/11/09 14:11:31' :: DATE) - EXTRACT(YEAR
                                                          FROM REPORTING_DATE)) >= 2

Athough i have cast the string:

2018/11/09 14:11:31 --> to date type

and:

EXTRACT(YEAR FROM ('2018/11/09 14:11:31' :: DATE) ) --> EXTRACT(YEAR FROM...) --> return double value type

But it still shows that message. Anyone could tell me what did i do wrong. and how could i fix it. Thank you.

标签： postgresql

Answer 1

你有一个错误的地方括号。它应该是

EXTRACT( YEAR FROM ('2018/11/09 14:11:31' :: DATE)
        ) - EXTRACT(YEAR FROM DATE REPORTING_DATE) >= 2
      --^                                       --^
   --put here                                  --not here