"An inch of time cannot be bought with an inch of gold."
- Chinese Proverb
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.
Above are the keywords you can utilize to extract the day, month and year from the SYSDATE.
The above example shows you how to get 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 will allow you to add or subtract months from a date.
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.
You can use the LAST_DAY command to find the end of the month or the first day of the next 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.
You can calculate the number of months between two dates by using the MONTHS_BETWEEN command.
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 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.
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 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.
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.
Look how easy it is to add a day to the SYSDATE. You can also add minutes.
Look how easy it is to get the difference in hours between two dates and times.
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.
Above you can seen an example of formatted dates using the TO_CHAR command.
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.
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!
Above we are using simple intervals. We have added a day, three months and five years to the SYSDATE.
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.
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.