PostgreSQL Date Conversion Function

Question

I have one challenge:

I have the following code in SQL Server and I want to move it to PostgreSQL DBMS.

I adapted it to PostgreSQL just like the following:

CREATE function public.SEMANA_ISO (fecha date) returns integer as $$ 
DECLARE 
    semana_ISO int= date_part ('week',fecha)+1
    -date_part('week',CAST(date_part('year',fecha) as CHAR(4)) + '0104');
BEGIN 
    IF (semana_ISO=0)
    THEN
    semana_ISO=public.SEMANA_ISO(CAST(date_part('year',fecha)-1
    AS CHAR(4)) + '12' + CAST(24 + date_part('day',fecha) AS CHAR(2)))+1;

    ELSIF ((date_part('month',fecha)=12) AND ((date_part('day',fecha)-date_part('day',fecha))>=28))
    THEN
    semana_ISO=1;

    END IF; 

RETURN semana_ISO;   
END;   
$$ LANGUAGE plpgsql;  

As you may see, I tried to make it look the most similar as in SQL Server is. However when I try to run this function and test it:

Select public.SEMANA_ISO('28/12/2014');

The DMBS shows many errors:

ERROR: operator does not exist: character + unknown
LINE 2: ...rt('week',CAST(date_part('year',fecha) as CHAR(4)) + '0104')

HINT: No operator matches the name and type of the arguments. You may need to add explicit type casts. 
QUERY: SELECT date_part ('week', date) +1 
-date_part ('week', CAST (date_part ('year', date) as CHAR (4)) + '0104') 
CONTEXT: PL / pgSQL function semana_iso (date) at line 5 during initialization of local variables in the statement block

What I try to do is the following. From the following input date format: dd/mm/yyyy I want to use the function above to show it as the next output format:mm-dd-yyyy

I have thought in doing a simpler function that could receive the date in the format given (dd/mm/yyyy) and using the set datestyle = mdy statement change it in the body of the function and finally print it or return it.

What do you suggest folks?

Your help & time is always appreciated!

Answer 1

A guess you may need a Function like below

CREATE function a_date_conv (fecha date) returns text as $$ 
select to_char(fecha, 'mm-dd-yyyy');  
$$ LANGUAGE sql;  

select a_date_conv('08/10/2014')

Result
-------
10-08-2014