I can not do a function that returns a Semester of a Date in PostgreSQL

Question

I can not do a function that returns a Semester of a Date in PostgreSQL

Navigation

#1 by (1 votes)

1

I'm trying to do a conversion that returns a semester of a date, but this error appeared here

CREATE or REPLACE FUNCTION semestre ( data timestamp )
RETURNS INTEGER AS
$$
BEGIN
    IF data <= 6
        THEN return 1;
    ELSE
        return 2;
    END IF;
RETURN -1;
END
$$
LANGUAGE 'plpgsql';

SELECT semestre(timestamp '16/06/2017');

Error message:

ERRO:operador não existe: timestamp without time zone <= integer
LINE 1: SELECT   $1  <= 6
                     ^
HINT:  Nenhum operador corresponde com o nome e o(s) tipo(s) de argumento(s) informados. Você precisa adicionar conversões de tipo explícitas.
QUERY:  SELECT   $1  <= 6
CONTEXT:  PL/pgSQL function "semestre" line 2 at IF

What could it be?

sql postgresql stored-procedures

asked by anonymous 16.06.2017 / 14:38

1 answer

Change page title with angle2 How to ignore an empty row in a table

score 1 · Accepted Answer

You are trying to compare a timestamp with an integer, you have to compare only the month.

do so:

 if date_part('month',data ) <=6

It would look like this:

CREATE or REPLACE FUNCTION semestre ( data timestamp )
RETURNS INTEGER AS
$$
BEGIN
    IF date_part('month',data ) <=6
        THEN return 1;
    ELSE
        return 2;
    END IF;
RETURN -1;
END
$$
LANGUAGE 'plpgsql';