Return the past timestamp values on the base of input hour to the user function

Workaround-1

create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
my_time timestamp;
begin

sql_stmt:=’SELECT CURRENT_TIMESTAMP – INTERVAL ‘ ||chr(39) ||P_HOUR||chr(39) || ‘ HOUR FROM DUAL’;
execute immediate sql_stmt into my_time;
dbms_output.put_line(sql_stmt);
return my_time;
end;
/

Workaround-2

create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
my_time timestamp;
begin

sql_stmt:=’SELECT CURRENT_TIMESTAMP – INTERVAL ”’ ||P_HOUR||”’ HOUR FROM DUAL’;
execute immediate sql_stmt into my_time;
dbms_output.put_line(sql_stmt);
return my_time;
end;
/

Testing :-

SQL> SELECT fun_test_timestamp(‘5’) FROM DUAL;

FUN_TEST_TIMESTAMP(‘5’)
—————————————————————————
18-JAN-00 11.30.56.000022000 PM

SQL> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP – INTERVAL ‘5’ HOUR FROM DUAL;

CURRENT_TIMESTAMP CURRENT_TIMESTAMP-INTERVAL’5’HOUR
—————————————————————————
19-JAN-00 04.45.30.127888 AM +05:30 18-JAN-00 11.45.30.127888000 PM +05:30

Advertisements

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