Return to Snippet

Revision: 3730
at September 7, 2007 19:53 by arunpjohny


Initial Code
CREATE FUNCTION datediff (diffType Character Varying(15), date1 Date, date2 Date) RETURNS integer AS $$
DECLARE
	YEAR_CONST Character Varying(15) := 'year';
	MONTH_CONST Character Varying(15) := 'month';
	DAY_CONST Character Varying(15) := 'day';

	diffInInterval Interval;
	diffInDoublePrecision Double Precision := 0;
	diffInInteger Integer := 0;
	dateTemp Date;
BEGIN
	
	diffInInterval := age(date2, date1);

	IF lower($1) = lower(YEAR_CONST) THEN
		diffInDoublePrecision := date_part('Year', diffInInterval);
	ELSEIF lower($1) = lower(MONTH_CONST) THEN
		diffInDoublePrecision := (date_part('Year', diffInInterval) * 12) + date_part('Month', diffInInterval);
	ELSEIF lower($1) = lower(DAY_CONST) THEN
		diffInDoublePrecision := endDate - startDate;
	END IF;

	diffInInteger := CAST(diffInDoublePrecision as Integer);
	RETURN diffInInteger;
END;
$$ LANGUAGE plpgsql;

Initial URL


Initial Description
A function which will give the difference between two dates in days, month or years.

Initial Title
datediff function in postgresql

Initial Tags


Initial Language
SQL