function year (date) does not exist

Question

function year (date) does not exist

Navigation

#1 by (1 votes)

0

I have the following query in mysql runs fine, but in postgres I mark error in YEAR (dias_festivos.fecha) = '2000', says:

function year (date) does not exist

Does anyone know why?

This is the query:

SELECT dias_festivos.id, dias_festivos.descripcion, IF(**YEAR(dias_festivos.fecha)='2000',** 
CONCAT_WS(' ', CONCAT_WS('/', LPAD(DAY(dias_festivos.fecha), 2, '0'), 
LPAD(MONTH(dias_festivos.fecha), 2, '0')),
'TODOS'), CONCAT_WS('/', LPAD(DAY(dias_festivos.fecha), 2, '0'), 
LPAD(MONTH(dias_festivos.fecha), 2, '0'), 
YEAR(dias_festivos.fecha))) AS fecha FROM dias_festivos WHERE dias_festivos.status = 1

sql postgresql

asked by Manuel Lira 16.12.2017 в 21:04

source

1 answer

How can I re-empty the array after there is no match? Payments with credit card returns cc_rejected_other_reason

score 1 · Answer 1

Postgresql does not have a YEAR function, you can change it by:

EXTRACT (field FROM source)

The EXTRACT function retrieves subfields such as the year or time of the   date / time values. source must be an expression of value of   type timestamp, time or interval. (The expressions of the type date   will be sent to the timestamp and, therefore, can be used   as well). field is an identifier or string that selects what   field extract from the source value. The EXTRACT function returns   Double precision type values.

... The EXTRACT function is mainly intended for computational processing.

Postgresql documentation on EXTRACT

Or you can use:

DATE_PART ('field', source)

The date_part function is the equivalent Ingres of EXTRACT , which is   the traditional standard SQL function.

Note that here the field parameter must be a value of   chain, not a name. Valid field names for date_part   are the same as for extract .

Postgresl documentation on DATE_PART

Example: VIEW DEMO

Let's apply both functions to a code example, on a column called FECHA which is of type DATE .

CREATE TABLE TEST_FECHA (
   ID_TEST     SERIAL PRIMARY KEY     NOT NULL,
   FECHA       DATE               NOT NULL
);

INSERT INTO TEST_FECHA (FECHA) VALUES ('2017-12-16'), ('20041120');

SELECT  ID_TEST, 
        EXTRACT(YEAR FROM  FECHA) CON_EXTRACT,  
        DATE_PART('YEAR', FECHA)  CON_DATEPART
FROM TEST_FECHA;

Result:

id_test    con_extract      con_datepart
1          2017             2017
2          2004             2004