Notice that the time element will still be displayed, but if you were to subtract two truncated dates with the same time, you would get an even number of days. One more observation is that the default for TRUNC would be the same as a format mask of DD, which simply eliminates the need to worry about the time in your calculations.

You can test the TRUNC function by truncating the SYSDATE to the nearest quarter by executing the code in Listing 7.20.

INPUT
Listing 7.20. Truncating to the quarter.

1: SELECT TO_CHAR(TRUNC(SYSDATE,'Q'),'MM/DD/YYYY HH:MM:SS AM')
2:     "Today's Date and Time"
3:  from DUAL

OUTPUT
Assuming today's date was 06/01/97, you would get the following output:

Today's Date and Time
----------------------
04/01/1997 12:00:00 AM

This result makes sense because June is in the second quarter, and the quarter ranges from 04/01/97 to 06/30/97. Truncating to the quarter gives the beginning date for the applicable quarter. You'll get the opportunity to test this function in the exercises at the end of the chapter.

The ADD_MONTHS Function

The ADD_MONTHS function adds or subtracts months from a date. Because this function is overloaded, which means that you can pass different datatypes to the same function or change the order of the parameters, you can specify the parameters in any order.

The Syntax for the ADD_MONTHS Function
Therefore, the syntax can be expressed in two ways:

ADD_MONTHS(date_passed,months_to_add)

or

ADD_MONTHS(months_to_add,date_passed)

If months_to_add is positive, it will add months into the future. If the months_to_add number is negative, it will subtract months from date_passed. You can specify months_to_add as a fraction, but Oracle completely ignores the fraction. You can go down to the day level by using other Oracle functions. Another caution is that Oracle will return the same day in the resulting calculation except if the last day in one month is the 31st (for example, March 31st)

and the resulting month does not have as many days (for example, April 30th would be the answer to adding one month). The following three examples in Listing 7.21 will provide the same result.

INPUT
Listing 7.21. Adding two months to SYSDATE.

1: SELECT ADD_MONTHS(SYSDATE,2) from DUAL;
2: SELECT ADD_MONTHS(2,SYSDATE) from DUAL;
3: SELECT ADD_MONTHS(SYSDATE,2.654) from DUAL;

OUTPUT
All of these (assuming the date is 06/02/97) will produce the following output:

ADD_MONTH
---------
02-AUG-97

You can see what happens for the last day of the month by adding one month to March 31st, as shown in Listing 7.22.

INPUT
Listing 7.22. Adding one month to SYSDATE.

SELECT ADD_MONTHS(TO_DATE('31-MAR-97'),1) from DUAL;

OUTPUT
This example will output

ADD_MONTH
---------
30-APR-97

Again, Oracle could not output April 31st because no such date exists.

The NEXT_DAY Function

The NEXT_DAY function returns the next date in the week for the day of the week specified after the input date. The time returned is the time specified by the input date when called.

The Syntax for the NEXT_DAY Function

NEXT_DAY(input_date_passed,day_name)

The NEXT_DAY function provides lots of possibilities. You could calculate anything from the first Monday of every month to each payday in a calendar year. You'll start off by testing the NEXT_DAY function on the SYSDATE function to find the next Monday. Assume the SYSDATE is June 3rd, 1997. Your own results will differ when you execute the code in Listing 7.23.

INPUT
Listing 7.23. Finding the first Monday after the current date and time.

1: SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Monday'),'MM/DD/YYYY HH:MM:SS AM')
2:      "Next_Day"
3:  from DUAL;

OUTPUT
The result returned for the SYSDATE of June 3rd, 1997 is

Next_Day
----------------------
06/09/1997 07:06:38 AM

ANALYSIS The first Monday after the date is June 9th, 1997. Because you are using the SYSDATE, the corresponding time value will be returned when the function is called.

You can find the first Monday for August, 1997 by executing the code in Listing 7.24.

INPUT
Listing 7.24. Finding the first Monday in the month of August.

1: SELECT TO_CHAR(NEXT_DAY('01-AUG-97','Monday'),'MM/DD/YYYY HH:MM:SS AM')
2:      "Next_Day"
3:  from DUAL;

OUTPUT
Your output will be

Next_Day
----------------------
08/04/1997 12:00:00 AM

ANALYSIS Although the first Monday in August is 08/04/97, is there a logic problem here? If you repeat the example, but use a month in which Monday is the first day of the month, what happens? Execute the code in Listing 7.25.

INPUT
Listing 7.25. Finding the first Monday in the month of September.

1: SELECT TO_CHAR(NEXT_DAY('01-SEP-97','Monday'),'MM/DD/YYYY HH:MM:SS AM')
2:      "Next_Day"
3:  from DUAL;

OUTPUT
Your output will be

Next_Day
----------------------
09/08/1997 12:00:00 AM

ANALYSIS The result is definitely not what you had in mind! The NEXT_DAY function returns the next day of the day specified. If the day of the week specified matches the input date, it will add one week to the input date. If you want to calculate the first occurrence of any day in the month, always use the end date of the previous month. Review the proper code in Listing 7.26.

INPUT
Listing 7.26. The proper method to find the first Monday in a given month.

1: SELECT TO_CHAR(NEXT_DAY('31-AUG-97','Monday'),'MM/DD/YYYY HH:MM:SS AM')
2:      "Next_Day"
3:  from DUAL;

OUTPUT
Your output will be

Next_Day
----------------------
09/01/1997 12:00:00 AM

You finally have the proper logic for what you intended to find originally!

The LAST_DAY Function

This function provides the last day of the given month. A very useful purpose would be to determine how many days are left in the given month.

The Syntax for the LAST_DAY Function

LAST_DAY(input_date_passed)

You will compute the last day in the month for when summer officially starts from 1997. Go ahead and execute the code in Listing 7.27.

INPUT
Listing 7.27. Finding the last day of the month starting summer.

1: SELECT TO_CHAR(LAST_DAY('30-JUN-97'),'MM/DD/YYYY HH:MM:SS AM') "Last_Day"
2:  from DUAL;

OUTPUT
Your output will be

Last_Day
------------------------
06/30/1997 12:06:00 AM