Using Datetime Functions
You use the datetime functions to get or process datetimes and timestamps (you ll learn about timestamps later in this chapter). Table 4-4 shows some of the datetime functions. In this table, x represents a datetime or a timestamp.
| Function | Description |
|---|---|
| ADD_MONTHS( x , y ) | Returns the result of adding y months to x . If y is negative, y months are subtracted from x . |
| LAST_DAY( x ) | Returns the last day of the month that contains x . |
| MONTHS_BETWEEN( x , y ) | Returns the number of months between x and y . If x appears before y on the calendar, the number returned is positive, otherwise the number is negative. |
| NEXT_DAY( x , day ) | Returns the datetime of the next day following x; day is specified as a literal string ” SATURDAY , for example. |
| ROUND( x [, unit ]) | Rounds x . By default, x is rounded to the beginning of the nearest day. You may supply an optional unit string to indicate the rounding unit. For example, YYYY rounds x to the first day of the nearest year. |
| SYSDATE() | Returns the current datetime set for the operating system on which the database resides. |
| TRUNC( x [, unit ]) | Truncates x . By default, x is truncated to the beginning of the day. You may supply an optional unit string that indicates the truncating unit. For example, MM truncates x to the first day of the month. |
You ll learn more about the functions shown in Table 4-4 in the following sections.
ADD_MONTHS()
You use ADD_MONTHS(x, y) to get the result of adding y months to x . If y is negative, y months are subtracted from x. The following example adds 13 months to January 1, 2005:
SELECT ADD_MONTHS('01-JAN-2005', 13) FROM dual; ADD_MONTH --------- 01-FEB-06 The following example subtracts 13 months from the January 1, 2005; notice that “13 months are added to this date using ADD_MONTHS():
SELECT ADD_MONTHS('01-JAN-2005', -13) FROM dual; ADD_MONTH --------- 01-DEC-03 You can provide a time and date to the ADD_MONTHS() function. For example, the following query adds two months to the datetime 7:15:26 PM on January 1, 2005:
SELECT ADD_MONTHS(TO_DATE('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS'), 2) FROM dual; ADD_MONTH --------- 01-MAR-05 The next query rewrites the previous example to convert the returned datetime from ADD_MONTHS() to a string using to TO_CHAR() with the format DD-MON-YYYY HH24:MI:SS:
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS'), 2), 'DD-MON-YYYY HH24:MI:SS') FROM dual; TO_CHAR(ADD_MONTHS(T -------------------- 01-MAR-2005 19:15:26 | Note | You can provide a time and date to any of the functions shown earlier in Table 4-4. |
LAST_DAY()
You use LAST_DAY(x) to get the date of the last day of the month that contains x . The following example displays the last date in January 2005:
SELECT LAST_DAY('01-JAN-2005') FROM dual; LAST_DAY( --------- 31-JAN-05 MONTHS_BETWEEN()
You use MONTHS_BETWEEN(x, y) to get the number of months between x and y . If x occurs before y in the calendar, the number returned by MONTHS_BETWEEN() is negative.
| Note | The ordering of the dates in your call to the MONTHS_BETWEEN() function is important: the later date must appear first if you want the result as a positive number. |
The following example displays the number of months between May 25, 2005, and January 15, 2005. Notice that since the later date (May 25, 2005) appears first, the result returned is a positive number:
SELECT MONTHS_BETWEEN('25-MAY-2005', '15-JAN-2005') FROM dual; MONTHS_BETWEEN('25-MAY-2005','15-JAN-2005') ------------------------------------------- 4.32258065 The next example reverses the same dates in the call to the MONTHS_BETWEEN() function, and therefore the returned result is a negative number of months:
SELECT MONTHS_BETWEEN('15-JAN-2005', '25-MAY-2005') FROM dual; MONTHS_BETWEEN('15-JAN-2005','25-MAY-2005') ------------------------------------------- 4.3225806 NEXT_DAY()
You use NEXT_DAY(x, day) to get the date of the next day following x ; you specify day as a literal string, such as SATURDAY.
The following example displays the date of the next Saturday after January 1, 2005:
SELECT NEXT_DAY('01-JAN-2005', 'SATURDAY') FROM dual; NEXT_DAY( --------- 08-JAN-05 ROUND()
You use ROUND(x[, unit]) to round x. By default, x is rounded to the beginning of the nearest day. If you supply an optional unit string, x is rounded to that unit; for example, YYYY rounds x to the first day of the nearest year. You can use many of the parameters shown earlier in Table 4-2 to round a datetime.
The following example uses ROUND() to round October 25, 2005, up to the first day in the nearest year, which is January 1, 2006. Notice that the date is specified as 25-OCT-2005 and is contained within a call to the function TO_DATE() function:
SELECT ROUND(TO_DATE('25-OCT-2005'), 'YYYY') FROM dual; ROUND(TO_ --------- 01-JAN-06 The next example rounds May 25, 2005, to the first day in the nearest month, which is June 1, 2005, because May 25 is closer to the beginning of June than it is to the beginning of May:
SELECT ROUND(TO_DATE('25-MAY-2005'), 'MM') FROM dual; ROUND(TO_ --------- 01-JUN-05 The next example rounds 7:45:26 P.M. on May 25, 2005, to the nearest hour , which is 8:00 P.M.:
SELECT TO_CHAR(ROUND(TO_DATE('25-MAY-2005 19:45:26', 'DD-MON-YYYY HH24:MI:SS'), 'HH24'), 'DD-MON-YYYY HH24:MI:SS') FROM dual; TO_CHAR(ROUND(TO_DAT -------------------- 25-MAY-2005 20:00:00 SYSDATE()
You use SYSDATE() to get the current datetime set in the operating system on which the database resides. In the following example, notice that the parentheses are omitted from the function call. This is because the SYSDATE() function accepts no parameters.
SELECT SYSDATE FROM dual; SYSDATE --------- 21-OCT-03
TRUNC()
You use TRUNC(x[, unit]) to truncate x. By default, x is truncated to the beginning of the day. If you supply an optional unit string, x is truncated to that unit; for example, MM truncates x to the first day in the month. You can use many of the parameters shown earlier in Table 4-2 to truncate a datetime.
The following example uses TRUNC() to truncate May 25, 2005, to the first day in the year, which is January 1, 2005:
SELECT TRUNC(TO_DATE('25-MAY-2005'), 'YYYY') FROM dual; TRUNC(TO_ --------- 01-JAN-05 The next example truncates May 25, 2005, to the first day in the month, which is May 1, 2005:
SELECT TRUNC(TO_DATE('25-MAY-2005'), 'MM') FROM dual; TRUNC(TO_ --------- 01-MAY-05 The next example truncates 7:45:26 P.M. on May 25, 2005, to the hour, which is 7:00 P.M.:
SELECT TO_CHAR(TRUNC(TO_DATE('25-MAY-2005 19:45:26', 'DD-MON-YYYY HH24:MI:SS'), 'HH24'), 'DD-MON-YYYY HH24:MI:SS') FROM dual; TO_CHAR(TRUNC(TO_DAT -------------------- 25-MAY-2005 19:00:00 
EAN: 2147483647
Pages: 217