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




Adds months to a date


Computes the last day of the month


Determines the number of months between two dates


Translates a time to a new time zone


Returns the date of the next specified weekday


Rounds a date/time value to a specified element


Returns the current date and time


Converts dates to strings


Converts strings and numbers to dates


Truncates a date/time value to a specific element


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 ...

Get Mastering Oracle SQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.