Chapter 5 – Date Functions
"An inch of time cannot be bought with an inch of gold."
- Chinese Proverb
Getting the System Date
The example above shows you how to get the system date. You must use the keyword sysdate and you need to get if from dual. Dual is a special table with a column called DUMMY that has a value of ‘X’. This 'X' column is used in selecting pseudo columns like SYSDATE.
Extracting the Day, Month, Year From the SYSDATE
Above are the keywords you can utilize to extract the day, month and year from the SYSDATE.
The Current_Timestamp
The above example shows you how to get the Current_Timestamp.
Extracting From the Current_Timestamp
Here are the keywords to extract the day, month, year, hour, minute and second from the Current_Timestamp.
The ADD_MONTHS Command
The ADD_MONTHS command will allow you to add or subtract months from a date.
Using the ADD_MONTHS Command to Add Years
The ADD_MONTHS command will allow you to add or subtract months from a date. You can also use this to add or subtract years. The Five_Years example above could have used 60 (months), but 12 * 5 can also be used.
Using the LAST_DAY Command
You can use the LAST_DAY command to find the end of the month or the first day of the next month.
Calculating the Days Until the End of the Month
You can use the LAST_DAY command to find the end of the month and then use it to find the days remaining until the end of the month. The example above shows you how to do it.
Calculating the Months Between Two Dates
You can calculate the number of months between two dates by using the MONTHS_BETWEEN command.
NEXT_DAY Command Finds a Future Day of the Week
This example calculates the date on the following Monday from our Order_Date. The Monday date will be later than the Order_Date. We could also put in another day of the week.
The ROUND Command
The ROUND command will round up or round down the Year, Month or Day. The examples above show the Year and the Month. Notice that the Year rounds to January 1, 2016 of the next year. This is because it is past June 30th so it rounds up. Notice that the Month rounds down to the first day of the month. This is because it isn't half way through the month yet.
Another ROUND Example
The ROUND command will round up or round down the Year, Month or Day. The examples above show the Year and the Month. Notice that the Year rounds to January 1, 2015 of the current year. This is because it is before July 1st so it rounds down. Notice that the Month rounds up to day one of the next month. This is because it is past half way through the month.
The TRUNC Command
The TRUNC command will truncate a date. This example uses the TRUNC command with the keyword year. Our SYSDATE is 10/14/2015 5:38:25. After the truncation, it turned back to 01/01/2015 12:00:00.
Another TRUNC Example
The TRUNC command will truncate a date. This example uses the TRUNC command with the keywords month and day. Our SYSDATE is 10/14/2015 5:38:25. After the truncation, the month truncated back to 10/01/2015 12:00:00 and the day moved back to Sunday 10/11/2015 12:00:00.
Adding Days and Minutes
Look how easy it is to add a day to the SYSDATE. You can also add minutes.
How to Get the Difference in Hours
Look how easy it is to get the difference in hours between two dates and times.
Add or Subtract Days Plus Format Dates and Dollars
SELECT
TO_CHAR(Order_Date, 'MM-DD-YYYY') as "Order_Date"
,TO_CHAR(Order_Date + 60, 'MM-DD-YYYY') as "Due Date"
,TO_CHAR(Order_Total, 'L999,999.99') as "Order_Total"
,TO_CHAR(Order_Date + 50, 'MM-DD-YYYY') as "Disc_Date"
,TO_CHAR(Order_Total *.98, 'L999,999.99') as "Disc_Amt"
FROM Order_Table
ORDER BY 1 ;
When you add or subtract from a Date you are adding/subtracting Days
Because Dates are stored internally on disk as integers, it makes it easy to add days to the calendar. In the query above we are adding 60 days to the Order_Date and 50 days to the Order_Date.
Formatting Date Example
Above you can seen an example of formatted dates using the TO_CHAR command.
A Summary of Math Operations on Dates
DATE - DATE = Interval (days between dates)
DATE + or - Integer = Date
SELECT
Order_Number as "Order #"
,Order_Total as "Order Total"
,TO_CHAR(Order_Date, 'MM/DD/YY') as "Order Date"
,TO_CHAR(Order_Date - 365, 'MM/DD/YYYY') as "Last Year"
,SYSDATE - Order_Date as "# of Days"
FROM Order_Table
A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date.
The EXTRACT Command
This is the Extract command. It returns a date part, such as a day, month or year, from a timestamp value or expression. It can be used in the SELECT list, the WHERE Clause, or the ORDER BY Clause!
Using Intervals
Above we are using simple intervals. We have added a day, three months and five years to the SYSDATE.
How a Simple Interval Handles Leap Year
This example works because we added 1 month to the date '2012-01-29' and we got '2012-02-29'. Because this was leap year, there actually is a date of February 29, 2012.
Troubleshooting Intervals – Invalid Dates Error
This example returns an error. Why? We have a date of '2011-01-29' and added 1-month to that, but there was no February 29th in 2011. Be careful with intervals.