Date Function

DATE FUNCTIONS
Sysdata	Current_data	Current_timestamp	Systimestamp
Localtimestamp	Dbtimezone	Sessiontimezone	To_char
To_date	Add_month	Month_between	Next_day
Last_day	Extract	Greatest	Least
Round	Trunc	New_time	Coalesc

Oracle default date format is DD-MON-YY.

We can change the default format to our desired format by using the following command.

SQL> alter session set nls_date_format = ‘DD-MONTH-YYYY’;

But this will expire once the session was closed.

SYSDATE

(This will give the current date and time)

SQL> select sysdate from dual;

SYSDATE

———–

24-DEC-06

CURRENT_DATE

(This will returns the current date in the session’s timezone)

SQL> select current_date from dual;

CURRENT_DATE

——————

24-DEC-06

CURRENT_TIMESTAMP

(This will returns the current timestamp with the active time zone information)

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

—————————————————

24-DEC-06 03.42.41.383369 AM +05:30

SYSTIMESTAMP

(This will returns the system date, including fractional seconds and time zone of the

database.)

SQL> select systimestamp from dual;

SYSTIMESTAMP

——————————————————

24-DEC-06 03.49.31.830099 AM +05:30

LOCALTIMESTAMP

(This will returns local timestamp in the active time zone information, with no time

zone information shown.)

SQL> select localtimestamp from dual;

LOCALTIMESTAMP

——————————————————

24-DEC-06 03.44.18.502874 AM

DBTIMEZONE

{This will returns the current database time zone in UTC format. (Coordinated Universal Time)}

SQL> select dbtimezone from dual;

DBTIMEZONE

—————

-07:00

SESSIONTIMEZONE

(This will returns the value of the current session’s time zone)

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

————————————

+05:30

TO_CHAR

This will be used to extract various date formats.

Syntax

to_char (date, format)

ORACLE

Date Function

CURRENT_DATE

CURRENT_TIMESTAMP

SYSTIMESTAMP

LOCALTIMESTAMP

DBTIMEZONE

SESSIONTIMEZONE

Comments

scienceclub

Click here to cancel reply.

CONTACT US

We are social:

Visitor Counter