Thread: Age Fucntion

Thanks for your time.
 
I'm having some difficulties with the age function.  I need to calculate the difference in days between the current date and a value provided to a function via an argument.  When I try to include my argument in the function, the function won't compile, if I replace my argument with a date value(replace dt_hiredate with '1/1/2007'), it compiles, and the function works as expected.
 
 
my code:
CREATE OR REPLACE FUNCTION "public"."function1" (dt_hiredate TIMESTAMP) RETURNS INTEGER AS
$body$
/* New function body */
DECLARE
BEGIN
    return date_part('year',age(timestamp dt_hiredate))*365 + date_part('day',age(timestamp dt_hiredate));
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
Compile message:
ERROR:  syntax error at or near "$1" at character 41
QUERY:  SELECT  date_part('year',age(timestamp  $1 ))*365 + date_part('day',age(timestamp  $2 ))
CONTEXT:  SQL statement in PL/PgSQL function "function1" near line 5

---

View Athletes' Collections with Live Search. See it!

brian stapel <brians_224@hotmail.com> writes:
>  return date_part('year',age(timestamp dt_hiredate))*365 + ...

This is not correct syntax, you should just write age(dt_hiredate).

I think you made an incorrect extrapolation from the syntax sometimes
used for literal timestamp constants, viz
        timestamp 'whatever'
We support that because it's in the SQL spec, but it doesn't generalize
to anything except literal constants.  If you needed to convert the
variable dt_hiredate to timestamp (which you do not, in this example,
because it already is that type), you'd write either
        cast(dt_hiredate as timestamp)
        dt_hiredate::timestamp
The former is the SQL-spec syntax for a runtime type conversion, the
latter a traditional Postgres abbreviation.

BTW, the cast and :: syntaxes work fine for literals too.  I tend to
avoid the type-name-first syntax for literals, just because it doesn't
generalize.

            regards, tom lane