Create a function to return current date and time in oracle

Question

I am new to oracle, I have to create a function that returns current date and time.

I am using this to return date;

SELECT CURRENT_DATE FROM dual;

Thanx

Solution 1

As an actual function you could do:

create or replace function getSysdate
return date is

  l_sysdate date;

begin

  select sysdate
    into l_sysdate
    from dual;

  return l_sysdate;

end;
/

Which you could test in SQLPlus as:

TEST>select getSysdate() from dual;

GETSYSDATE
----------
2010-01-04

Not sure why you would want this versus just having sysdate in your code. The Oracle date datatype includes the time portion.

Solution 2

CURRENT_DATE returns the date and time of the session. SYSDATE returns the date and time of the database. These values may be different because we can change our session's timezone using ALTER SESSION. You probably ought to be using SYSDATE because it returns a consistent value, although without knowing your business context it is hard to be certain.

From your question I suspect you don't realise that Oracle date pseudo-columns include a time element. Try this:

alter session set nls_date_format='dd-mon-yyyy hh24Lmi:ss'
/
select current_date from dual
/
select sysdate from dual
/

There is not a lot of point in wrapping one of those pseudo-columns in your own user-defined function. The one time I seriously considered it was to make it easier to inject times into some automated unit tests. But I never convinced myself that this facility would justify not using the standard approach.

edit

The solution in the accepted answer works but has a lot of unnecessary baggage. All that additional PL/SQL runs 2-3 times slower than a straight select sysdate from dual;. It is true that these are very small differences in absolute terms - milliseconds, if that. But in a busy system with lots of calls to getSysdate() all those milliseconds could add up to a big chunk of time. A better solution would be to replace all that code with a plain return sysdate; This is slightly slower than calling sysdate directly but only by a little.

Expanding on dpbradley's comment, I have knocked up a function which allows us to substitute a different clocktime from the database, for the purposes of testing. I am storing my alternate datetime in the CLIENT_INFO namespace in the default context; if I were implementing this in a production system I would build a dedicated user defined context for it.

So here is my take on the getSysdate() function...

SQL> create or replace function myGetSysdate
  2      ( p_alt_date in varchar2 := null )
  3  return date is
  4  begin
  5      if p_alt_date is null then
  6          return sysdate;
  7      else
  8          return to_date(sys_context('userenv', p_alt_date)
  9                                        , 'dd-mon-yyyy hh24:mi:ss');
 10      end if;
 11  end;
 12  /

Function created.

SQL> 

Here is how we set the alternate datetime...

SQL> exec dbms_application_info.set_client_info('01-DEC-2010 12:12:12')

PL/SQL procedure successfully completed.

If no parameter is passed it returns sysdate (the default and preferred option).

SQL> select getsysdate from dual
  2  /

GETSYSDATE
-----------------
05-JAN-2010 16:25

SQL> 

If we pass a context namespace when we call the function we get the alternate datetime....

SQL> select mygetsysdate('CLIENT_INFO') from dual
  2  /

MYGETSYSDATE('CLI
-----------------
01-DEC-2010 12:12

SQL>

Solution 3

try:

SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

if you need it in a certain format. More options here:

http://www.psoug.org/reference/date_func.html