Mastering Oracle SQL

Date Manipulation

Date arithmetic is an important aspect of our day-to-day life. We find the age of a person by subtracting his date of birth from today’s date. We compute the date a warranty expires by adding the warranty period to the purchase date. Drivers’ license expirations, bank interest calculation, and a host of other things all depend on date arithmetic. It is extremely important for any database to support such common date arithmetic operations.

Oracle provides some very good date arithmetic features. Not only can we add and subtract dates, but Oracle also provides a number of other helpful functions for manipulating date values. We discuss these features in detail in this section. Table 6-2 lists various date manipulation functions provided by Oracle SQL.

Table 6-2. Date functions

Function	Use
ADD_MONTHS	Adds months to a date
LAST_DAY	Computes the last day of the month
MONTHS_BETWEEN	Determines the number of months between two dates
NEW_TIME	Translates a time to a new time zone
NEXT_DAY	Returns the date of the next specified weekday
ROUND	Rounds a date/time value to a specified element
SYSDATE	Returns the current date and time
TO_CHAR	Converts dates to strings
TO_DATE	Converts strings and numbers to dates
TRUNC	Truncates a date/time value to a specific element

Addition

Adding two dates doesn’t make sense. However, we can add days, months, years, hours, minutes, and seconds to a date to generate a future date and time. The “+” operator allows ...