“Pretty” Date Format Function

I’m not sure who originally wrote this function (probably Tom Kyte), but I needed it yesterday and couldn’t find it. Thankfully Chris Beck tracked it down in some other code I wrote.  I made a few enhancements, but the bulk of the function is the same.  The output is almost identical to the “SINCE” date format available in Application Express.  So, if you pass in a date in the past to this function, the result will be similar to “25 seconds ago”, “1 minute ago”, “5 months ago”, etc.

Function

create or replace function date_text_format (p_date in date)
return varchar2
as
	x	varchar2(255);
begin
	x := 	case
				when sysdate-p_date < 1/1440
					then round(24*60*60*(sysdate-p_date)) || ' seconds'
				when sysdate-p_date < 1/24
					then round(24*60*(sysdate-p_date)) || ' minutes'
				when sysdate-p_date < 1
					then round(24*(sysdate-p_date)) || ' hours'
				when sysdate-p_date < 14
					then trunc(sysdate-p_date) || ' days'
				$IF $$BRITISH $THEN
					when mod(trunc(sysdate-p_date),14) = 0
						then trunc(sysdate-p_date) / 14 || ' fortnights'
				$END
				when sysdate-p_date < 60
					then trunc((sysdate-p_date)/7) || ' weeks'
				when sysdate-p_date < 365
					then round(months_between(sysdate,p_date)) || ' months'
				else round(months_between(sysdate,p_date)/12,1) || ' years'
		   end;
	x:= regexp_replace(x,'(^1 &#91;&#91;:alnum:&#93;&#93;{4,10})s','\1');
	x:= x || ' ago';
	return x;
end date_text_format;
/&#91;/sourcecode&#93;
<h3>Examples</h3>
select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;
select date_text_format(sysdate - 120) the_date from dual;
select date_text_format(sysdate - 365) the_date from dual;
--------------------------------------------------------------------
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago