Calculate Age or Birthday

CREATE or replace FUNCTION CalculateAge(m_birthday date, m_datetoday date)
RETURNS INTEGER
AS
$$
BEGIN

RETURN

(
select
(
CASE
WHEN dateadd('y', datediff ('y', m_birthday, m_datetoday), m_birthday) > m_datetoday
THEN datediff ('y', m_birthday, m_datetoday) - 1
ELSE datediff ('y', m_birthday, m_datetoday)
END)

);

END;
$$ language 'plpgsql';

-- see my previous post on dateadd and datediff

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;

DATEADD

MSSQL
select dateadd(year,1, '2004-2-29')

PostgreSQL
select '2004-2-29'::date + cast('1 years' as interval)

MSSQL:
select dateadd(month,1,'2004-2-29')


PostgreSQL:
select '2004-2-29'::date + cast('1 months' as interval)

MSSQL:
select dateadd(month,1, '1976-11-05 6:29 AM')

PostgreSQL:
select '2006-01-06 6:29 AM'::timestamptz + cast('1 months' as interval)

MSSQL:
select dateadd(day,1,'2006-01-06 6:29 AM')
-- or --
select convert(datetime,'2006-01-06 6:29 AM') + 1


PosgtgreSQL:
select '2006-01-06 6:29 AM'::timestamp + cast('1 days' as interval);

------------------------------------------------------------------------
------------------------------------------------------------------------
MS SQL equivalent compatible DATEADD function

-- Function: dateadd(character varying, integer, date)

-- DROP FUNCTION dateadd(character varying, integer, date);

CREATE OR REPLACE FUNCTION dateadd(character varying, integer, date)
RETURNS date AS
$BODY$

DECLARE
p_Interval ALIAS FOR $1;
p_N ALIAS FOR $2;
p_Date ALIAS FOR $3;




BEGIN
if p_Interval = 'm' then
return p_Date + cast(p_N || ' months' as interval);
elseif p_Interval = 'y' then
return p_Date + cast(p_N || ' years' as interval);
else
raise exception 'dateadd interval parameter not supported';
-- raise exception 'hello';
return null;
end if;
END;
$BODY$
LANGUAGE 'plpgsql';