Message-ID: <04af01c427aa$ddb75640$cd855140@h2ttp>
From: "Erik Aronesty" <erik@zoneedit.com>
To: <pgsql-sql@postgresql.org>
References: <GOEGKICPFOPNLIEIHGFJGEPBCKAA.postgresql@thegatelys.com>
	<000601c4279b$04c12f00$6400a8c0@Dell4500>
Subject: datediff script
Date: Wed, 21 Apr 2004 10:13:25 -0400
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

This is a not-quite complete implementation of the SY/MS sql datediff.  The
months_between function can be extrapolated from it as well.  I looked for
it on forums, etc. and all I found were people complaining about the lack of
an example.  Please post fixes/changes or a link to a better one... if you
know of it.

CREATE OR REPLACE FUNCTION public.datediff(varchar, timestamp, timestamp)
  RETURNS int4 AS
'
DECLARE
 arg_mode alias for $1;
 arg_d2 alias for $2;
 arg_d1 alias for $3;
BEGIN

if arg_mode = \'dd\' or arg_mode = \'d\' or arg_mode = \'y\' or arg_mode =
\'dy\' or arg_mode = \'w\' then
 return cast(arg_d1 as date) - cast(arg_d2 as date);
elsif arg_mode = \'ww\' then
        return ceil( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0);
elsif arg_mode = \'mm\' OR arg_mode = \'m\' then
 return 12 * (date_part(\'year\',arg_d1) - date_part(\'year\',arg_d2))
      + date_part(\'month\',arg_d1) - date_part(\'month\',arg_d2)
             + case when date_part(\'day\',arg_d1) >
date_part(\'day\',arg_d2)
                    then 0
                    when date_part(\'day\',arg_d1) =
date_part(\'day\',arg_d2) and cast(arg_d1 as time) >= cast(arg_d2 as time)
                    then 0
                    else -1
               end;
elsif arg_mode = \'yy\' OR arg_mode = \'y\' OR arg_mode = \'yyyy\' then
 return (cast(arg_d1 as date) - cast(arg_d2 as date)) / 365;
end if;

END;
'
  LANGUAGE 'plpgsql' VOLATILE;