DATE and TIMESTAMP are two datatypes which are
used to store date and time in database.
Date included year,month,day and time. Here are the functions to manipulate date
type data
Date Functions :
1) SYSDATE
-
It
returns current date or system date (date as per oracle database server)
Example :
SQL>select sysdate from dual;
SYSDATE
--------
10-NOV-2012
We can add and subtract numbers or constants as well as other
dates from dates. Oracle interprets number constants in arithmetic date
expressions as numbers of days.
Example:
SQL>select sysdate-1 from dual;
SYSDATE-1
---------
09-NOV-2012
SQL>select sysdate+1 from
dual;
SYSDATE+1
---------
11-NOV-2012
SQL>select sysdate+7 from
dual;
SYSDATE+7
---------
17-NOV-12
2) NEXT_DAY ()
NEXT_DAY returns the date of the
next occurrence of given weekday
Syntax :
NEXT_DAY (date, weekday)
The second argument weekday must
be a day of the week in the date language of your session, either the full name
or the abbreviation.
Examples :
SQL>select
sysdate, next_day(sysdate,'Mon') from dual;
SYSDATE NEXT_DAY(
--------- ---------
12-NOV-2012 19-NOV-2012
SQL>select
sysdate, next_day(sysdate,'Fri') from dual;
SYSDATE NEXT_DAY(
--------- ---------
12-NOV-12 16-NOV-12
SQL>select
next_day('01-Jan-2013','Wed') from dual;
NEXT_DAY(
---------
02-JAN-2013
3)
LAST_DAY(date)
LAST_DAY returns the
date of the last day of the month.
Examples :
Last day of current
month
SQL>select
sysdate,last_day(sysdate) from dual;
SYSDATE LAST_DAY(
--------- ---------
12-NOV-12 30-NOV-12
Last date/day of Feb’2013
SQL>select
last_day('01-Feb-2013') from dual;
LAST_DAY(
---------
28-FEB-13
The following query
return current date, last date of this month and how many days remaining in
this month from today.
SQL>SELECT
SYSDATE, LAST_DAY(SYSDATE) "Last
date", LAST_DAY(SYSDATE) - SYSDATE
"RemainingDays " FROM DUAL;
SYSDATE Last
date RemainingDays
--------- ---------
--------------
12-NOV-12 30-NOV-12 18
4) MONTHS_BETWEEN
-
It returns number of months available between
given two dates.
Syntax
:
MONTHS_BETWEEN(date1,
date2 )
It returns number of months available between given two dates date1 and
date2
Examples :
SQL>select months_between('15-Dec-2012','15-Jan-2012') from dual;
MONTHS_BETWEEN('15-DEC-2012','15-JAN-2012')
-------------------------------------------
11
No comments:
Post a Comment