Saturday, December 08, 2007

PostgreSQL DATEDIFF

version 8.0 and higher

CREATE OR REPLACE FUNCTION datediff(p_Interval "varchar", p_DateFrom date, p_DateTo date)
RETURNS int4 AS
$BODY$




BEGIN
if p_Interval = 'm' then
return ((date_part('y',p_DateTo) * 12) + date_part('month',p_DateTo))
- ((date_part('y',p_DateFrom) * 12) + date_part('month',p_DateFrom));
elseif p_Interval = 'y' then
return date_part('y',p_DateTo) - date_part('y',p_DateFrom);
else
raise exception 'Datediff: Interval not supported';
return 0;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;





before version 8.0



CREATE OR REPLACE FUNCTION datediff("varchar", date, date)
RETURNS int4 AS
$BODY$

DECLARE
p_Interval ALIAS FOR $1;
p_DateFrom ALIAS FOR $2;
p_DateTo ALIAS FOR $3;




BEGIN
if p_Interval = 'm' then
return ((date_part('y',p_DateTo) * 12) + date_part('month',p_DateTo))
- ((date_part('y',p_DateFrom) * 12) + date_part('month',p_DateFrom));
elseif p_Interval = 'y' then
return date_part('y',p_DateTo) - date_part('y',p_DateFrom);
else
raise exception 'Datediff: Interval not supported';
return 0;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Labels:

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home