Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

“Pretty” Date Format Function

Posted by Viral Sarvaiya on January 8, 2010

In one of my project I have the requirement that I have to show the time when how many time ago this photo was uploaded means “25 seconds ago”, “1 minute ago”, “5 months ago”, etc.

I found this superb code from a blog. So I put it here to share this.

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 [[:alnum:]]{4,10})s','\1');
x:= x || ' ago';
return x;
end date_text_format;
/

Examples :

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

this is the function is of the ORACLE.

for more details please click here

Function :

Advertisements

One Response to ““Pretty” Date Format Function”

  1. EdgarLO said

    did yu hear about new iphone 4.0? For me, one of the best features in iPhone OS 4.0 is its multitasking feature. And you?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: