Date Functions and Operators.
To see
the system date and time use the following functions :
CURRENT_DATE
:returns the current date in the session time zone, in a value in the Gregorian
calendar of datatype DATE
SYSDATE
:Returns the current date and time.
SYSTIMESTAMP
:The SYSTIMESTAMP function returns the
system date, including fractional seconds and time zone of the database. The
return type is TIMESTAMP WITH TIME ZONE.
SYSDATE Example
To see
the current system date and time give the following query.
Select sysdate from dual;
SYSDATE
-------
8-AUG-03
The
format in which the date is displayed depends on NLS_DATE_FORMAT parameter.
For
example set the NLS_DATE_FORMAT to the following format
Alter session set NLS_DATE_FORMAT=’DD-MON-YYYY HH:MIpm’;
Then give
the give the following statement
Select sysdate from dual;
SYSDATE
------------------
8-AUG-2003 03:05pm
The
default setting of NLS_DATE_FORMAT is DD-MON-YY
CURRENT_DATE Example
To see
the current system date and time with time zone use CURRENT_DATE function
ALTER SESSION SET TIME_ZONE =
'-4:0';
ALTER SESSION SET
NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE,
CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
---------------
--------------------
-04:00
22-APR-2003 14:15:03
ALTER SESSION SET TIME_ZONE =
'-7:0';
SELECT SESSIONTIMEZONE,
CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
---------------
--------------------
-07:00
22-APR-2003 09:15:33
SYSTIMESTAMP Example
To see
the current system date and time with fractional seconds with time zone give
the following statement
Select systimestamp from dual;
SYSTIMESTAMP
-------------------------------
22-APR-03 08.38.55.538741 AM
-07:00
DATE FORMAT MODELS
To translate the date into a different format string you can
use TO_CHAR function with date format. For example to see the current day you
can give the following query
To translate a character value, which is in format other than the
default date format, into a date value you can use TO_DATE function with date
format to date
Select to_char(sysdate,’DAY’)”Today” FROM DUAL;
TODAY
-------
THURSDAY
Give the
following statement
Select
to_char(sysdate,’Day, ddth Month, yyyy’)”Today” from dual;
TODAY
------------------------
Friday, 7th March, 2014
For
example you want to see hire dates of all employee in the following format
Friday,
8th August, 2003
Then give
the following query.
Select
to_char(hire_date,’Day, ddth Month, yyyy’) from emp;
TO_DATE Example
To_Date function is used to convert strings into date values. For
example you want to see what was the day on 15-aug-1947. The use the to_date
function to first convert the string into date value and then pass on this
value to to_char function to extract day.
Select
to_char(to_date(’15-aug-1947’,’dd-mon-yyyy’),’Day’)
from dual;
TO_CHAR(
--------
Friday
To see
how many days have passed since 15-aug-1947 then give the following query
Select
sysdate-to_date(’15-aug-1947’,’dd-mon-yyyy’)
from dual;
Now we
want to see which date will occur after 45 days from now
Select sysdate+45 from dual;
SYSDATE
-------
06-JUN-2003
ADD_MONTHS
To see
which date will occur after 6 months from now, we can use ADD_MONTHS function
Select ADD_MONTHS(SYSDATE,6) from dual;
ADD_MONTHS
----------
22-OCT-2003
MONTHS_BETWEEN
To see
how many months have passed since 15-aug-1947, use the MONTHS_BETWEEN function.
Select months_between(sysdate,to_date(’15-aug-1947’))
from dual;
Months
------
616.553
To
eliminate the decimal value use truncate function
LAST_DAY
To see
the last date of the month of a given date, Use LAST_DAY function.
Select
LAST_DAY(sysdate) from dual;
LAST_DAY
--------
31-AUG-2003
NEXT_DAY
To see
when the next Saturday is coming, use the NEXT_DAY function.
Select next_day(sysdate) from dual;
NEXT_DAY
-----------
09-AUG-2003
EXTRACT
An EXTRACT datetime function extracts and returns the value of a
specified datetime field from a datetime or interval value expression. When you
extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing
the appropriate time zone name or abbreviation
The
syntax of EXTRACT function is
EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE /
TIMEZONE FROM DATE)
Example
The following demonstrate the usage of EXTRACT function
to extract year from current date.
Select extract(year from sysdate) from dual;
EXTRACT
-------
2003