FAQ
How to create function which returns persons age in years?

Function parameters:

ldDob - Day Of birth
ldDate - Day where age is returned


I tried

CREATE OR REPLACE FUNCTION public.age(date, date, out integer) IMMUTABLE AS
$_$
SELECT floor(INT($2::text::integer-$1::text::integer)/10000);
$_$ language sql


but got

ERROR: syntax error at or near "("


In VFP I can use

RETURN floor(INT((VAL(DTOS(ldDate))-VAL(DTOS(ldDob))))/10000)

or

RETURN (year(ldDate) - year(ldDOB) - ;
iif( str(month(ldDate),2) + str(day(tdDate),2) < ;
str(month(tdDOB),2) + str(day(tdDOB),2), 1, 0) )


Andrus.

Search Discussions

  • Alexander Staubo at May 14, 2007 at 8:04 pm

    On 5/14/07, Andrus wrote:
    How to create function which returns persons age in years?
    [snip]

    What's wrong with age()?

    # select age('1879-03-14'::date);
    age
    ------------------
    128 years 2 mons
    # select extract(year from age('1879-03-14'::date));
    date_part
    -----------
    128

    You can give age() two arguments to calculate the difference between
    two dates to get an interval:

    # select age('1955-04-18'::date, '1879-03-14'::date);
    age
    -----------------------
    76 years 1 mon 4 days

    If you subtract two date values you will get the number of days
    instead of an interval.

    Documentation:

    http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html

    Alexander.
  • Jon Sime at May 14, 2007 at 8:08 pm

    Andrus wrote:
    How to create function which returns persons age in years?

    Function parameters:

    ldDob - Day Of birth
    ldDate - Day where age is returned


    I tried
    CREATE OR REPLACE FUNCTION public.age(date, date, out integer) IMMUTABLE AS
    $_$
    SELECT floor(INT($2::text::integer-$1::text::integer)/10000);
    $_$ language sql
    There's already an age(timestamp [, timestamp]) function available for this:

    select age('1912-06-23'::date);
    or
    select age(now()::date, '1912-06-23'::date);

    And if you want just the number of years, use date_part to extract just
    that piece:

    select date_part('year', age(now()::date, '1912-06-23'::date));

    Based on this and your other question about functions that followed, you
    may want to read the Date and Time Functions section of the docs:

    http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html

    -Jon

    --
    Senior Systems Developer
    Media Matters for America
    http://mediamatters.org/
  • Rich Shepard at May 14, 2007 at 8:32 pm

    On Mon, 14 May 2007, Andrus wrote:

    How to create function which returns persons age in years?
    Look at the PostgreSQL docs for "Date/Time Functions and Operators."
    You'll find the syntax for AGE() there.

    Rich

    --
    Richard B. Shepard, Ph.D. | The Environmental Permitting
    Applied Ecosystem Services, Inc. | Accelerator(TM)
    <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 14, '07 at 11:44a
activeMay 14, '07 at 8:32p
posts4
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase