Transcript Date Functions

Date Functions
What Will I Learn?
• single-row Date functions
– MONTHS_BETWEEN
– ADD_MONTHS, NEXT_DAY, LAST_DAY
– ROUND , TRUNC that operate on date data
• proper use of the arithmetic operators with dates
• the use of SYSDATE and date functions
• State the implications for world businesses to be
able to easily manipulate data stored in date
format
home back first prev next last
2
Why Learn It?
• Have you ever wondered how many days remain
in the school year or how many weeks there are
until graduation?
– Because the Oracle database stores dates as
numbers, it's easy to perform calculations on
dates using addition and subtraction.
• Businesses depend on being able to use date
functions to schedule payrolls and payments,
track employee performance reviews and years
of service, or keep track of orders and shipments.
– All of these business needs are easily handled
using simple SQL date functions.
home back first prev next last
3
DATE
• The default display and input format
– DD-MON-RR -- that is, 02-DEC-99.
– For Chinese character set, that is 02-12月-99
• Valid Oracle dates are
– Between January 1, 4712 B.C., and December 31,
9999 A.D.
• Oracle database stores dates internally with a
numeric format, representing the century, year,
month, day, hours, minutes, and seconds.
– Yes, DATE data type includes hours, minutes, and
seconds
home back first prev next last
4
SYSDATE
• When a date value is stored into a table
using the default input format, the century
information is picked up from the
SYSDATE function.
• SYSDATE is a date function that returns
the current database server date and time.
• To display the current date, use the DUAL
table.
SELECT SYSDATE
FROM DUAL;
home back first prev next last
5
Notice
• The DATE data type always stores year
information as a four-digit number
internally, such as 1996 or 2004, not just
as 96 or 04.
• But the century component is not
displayed by default.
home back first prev next last
6
A problem
home back first prev next last
7
date functions
• The date functions shown
in the table operate on
Oracle dates.
• All of the date functions
return a value with a
DATE data type except
the
MONTHS_BETWEEN
function
• which returns a numeric
data type value.
home back first prev next last
8
MONTHS_BETWEEN
• MONTHS_BETWEEN returns number of months between dates
date1 and date2.
• If date1 is later than date2, then the result is positive.
• If date1 is earlier than date2, then the result is negative.
• If date1 and date2 are either the same days of the month or both
last days of months, then the result is always an integer.
• Otherwise Oracle Database calculates the fractional portion of
the result based on a 31-day month and considers the
difference in time components date1 and date2.
home back first prev next last
9
MONTHS_BETWEEN
SELECT MONTHS_BETWEEN (TO_DATE('02-031995','MM-DD-YYYY'), TO_DATE('01-031995','MM-DD-YYYY') ) "Months" FROM DUAL;
MONTHS_BETWEEN
(TO_DATE('02-28-1995','MM-DD-YYYY'),
TO_DATE('01-31-1995','MM-DD-YYYY') )
MONTHS_BETWEEN
(TO_DATE('02-27-1995','MM-DD-YYYY'),
TO_DATE('01-31-1995','MM-DD-YYYY') )
home back first prev next last
10
MONTHS_BETWEEN
MONTHS_BETWEEN
(TO_DATE('02-28-1995','MM-DD-YYYY'),
TO_DATE('01-30-1995','MM-DD-YYYY') )
MONTHS_BETWEEN
(TO_DATE('02-04-1995','MM-DD-YYYY'),
TO_DATE('01-03-1995','MM-DD-YYYY') )
home back first prev next last
11
MONTHS_BETWEEN
MONTHS_BETWEEN
(TO_DATE('02-03-1995','MM-DD-YYYY'),
TO_DATE('01-04-1995','MM-DD-YYYY') )
MONTHS_BETWEEN 计算逻辑总结：
如果日相同，或都为该月最后一天，结果为整数。
否则，先计算整月数，然后用日之差除以31作为小
数部分。
 即(年1-年2)*12+（月1-月2）+（日1-日2）/31。
 这种计算方法整月比较可靠，但如果有小数，则可能出现
上面第四个例子中的不合理现象。
home back first prev next last
12
ADD_MONTHS
• ADD_MONTHS returns the date date plus integer
months.
• The return type is always DATE
• If date is the last day of the month or if the resulting
month has fewer days than the day component of
date, then the result is the last day of the resulting
month.
• Otherwise, the result has the same day component
as date.
home back first prev next last
13
ADD_MONTHS
SELECT ADD_MONTHS(TO_DATE('01-03-1995','MM-DDYYYY'),1) FROM DUAL;
03-2月 –95
ADD_MONTHS(TO_DATE('01-31-1995','MM-DD-YYYY'),1)
28-2月 –95
ADD_MONTHS(TO_DATE('01-29-1995','MM-DD-YYYY'),1)
28-2月 –95
ADD_MONTHS(TO_DATE('02-28-1995','MM-DD-YYYY'),1)
31-3月 -95
ADD_MONTHS(TO_DATE('02-27-1995','MM-DD-YYYY'),1)
27-3月 -95
home back first prev next last
14
NEXT_DAY
• NEXT_DAY returns the date of the first weekday named by char
that is later than the date date.
• The return type is always DATE
• The return value has the same hours, minutes, and seconds
component as the argument date.
• The argument char must be a day of the week in the date
language of your session, either the full name or the
abbreviation.
• The minimum number of letters required is the number of letters
in the abbreviated version. Any characters immediately
following the valid abbreviation are ignored.
home back first prev next last
15
NEXT_DAY
• For example
SELECT NEXT_DAY('02-FEB-2001','TUESDAY')
"NEXT DAY" FROM DUAL;
– For Chinese character set
SELECT Next_Day(TO_DATE('2007-3-11','YYYYMM-DD'),'星期五') FROM DUAL;
home back first prev next last
16
LAST_DAY
• LAST_DAY returns the date of the last day of the month that
contains date.
• The return type is always DATE.
– SELECT Last_Day(TO_DATE('2007-3-11','YYYY-MM-DD')) FROM DUAL;
home back first prev next last
17
ROUND (date)
• ROUND returns date rounded to the unit specified by
the format model fmt.
• The value returned is always of datatype DATE.
• If you omit fmt, then date is rounded to the nearest
day.
home back first prev next last
18
ROUND (date)
四舍五入到年或月最为常用
即使2月只有28天，ROUND函数也只在
16日之后（包括16日）才进到下一个月。
周一、二、三四舍五入到上个周日；周四、五、六、七到本周日；
home back first prev next last
19
TRUNC (date)
• The TRUNC (date) function returns date with the time
portion of the day truncated to the unit specified by
the format model fmt.
• The value returned is always of datatype DATE
• If you omit fmt, then date is truncated to the nearest
day.
home back first prev next last
20
TRUNC (date)
For more details of fmt , you can refer to Oracle®
Database SQL Reference or the next slide.
home back first prev next last
21
Format Models for the ROUND and
TRUNC Date Functions
The starting day of the week
used by the format models
DAY, DY, and D is specified
implicitly by the initialization
parameter
NLS_TERRITORY.
home back first prev next last
22
An Example Query
SELECT employee_id, hire_date,
MONTHS_BETWEEN(SYSDATE, hire_date) AS
TENURE,
ADD_MONTHS (hire_date, 6) AS REVIEW,
NEXT_DAY(hire_date, 'FRIDAY'),
LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) >36;
home back first prev next last
23
arithmetic operators
• +
– date1+n return the day n days after date1
SELECT
TO_DATE('01-01-1995','MM-DDYYYY')+31 "Days" FROM DUAL;
01-2月 -95
• – date1-n return the day n days before date1
– date1-date2 return the days between date1 and date2
home back first prev next last
24
Terminology
• Key terms used in this lesson include:
– ADD_MONTHS
– LAST_DAY
– MONTHS_BETWEEN
– NEXT_DAY
– SYSDATE
home back first prev next last
25
Summary
• Select and apply the single-row functions
– MONTHS_BETWEEN
– ADD_MONTHS,NEXT_DAY, LAST_DAY
– ROUND, and TRUNC that operate on date data
• Explain how date functions transform Oracle
dates into date data or a numeric value
• Demonstrate proper use of the arithmetic
operators with dates
• Demonstrate the use of SYSDATE and date
functions
• State the implications for world businesses to be
able to easily manipulate data stored in date
format
home back first prev next last
26