Working with Dates in PL/SQL（PL/SQL中使用日期）(四)
splay these names is determined by the NLS_DATE_LANGUAGE setting, which can also be specified as the third argument in the call to TO_CHAR, as in
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE,
'Day, DDth Month YYYY',
'NLS_DATE_LANGUAGE=Spanish'));
END;
/
Domingo , 07TH Agosto 2011
Use TO_CHAR to display the full names of both the day and the month in the date—but without all those extra spaces in the date-as-string. Oracle Database, by default, pads the string with spaces to match the maximum length of the day or the month. In most situations, you don’t want to include that extra text, and Oracle Database offers a format element modifier, FM, to control blank and zero padding. In the following block, I prefix the format mask with FM and remove the 0 (before 7) and extra spaces after August:
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE,
'FMDay, DDth Month YYYY'));
END;
/
Sunday, 7TH August 2011
You can also use the format mask to extract just a portion of, or information about, the date, as shown in the following examples:
What quarter is it
TO_CHAR (SYSDATE, 'Q')
What is the day of the year (1-366) for today’s date
TO_CHAR (SYSDATE, 'DDD')
What are the date and time of a DATE variable (This is a very common requirement, because the default format mask for a date does not include the time component, which means that asking DBMS_OUTPUT.PUT_LINE to display a date leaves out the time.)
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE,
'YYYY-MM-DD HH24:MI:SS'));
END;
/
You can also use EXTRACT to extract and return the value of a specified element of a date. For example
What year is it
EXTRACT (YEAR FROM SYSDATE)
What is the day for today’s date
EXTRACT (DAY FROM SYSDATE)
To convert a string to a date, use the TO_DATE or the TO_TIMESTAMP built-in function. Provide the string and Oracle Database returns a date or a time stamp, using the default format mask for the session:
DECLARE
l_date DATE;
BEGIN
l_date := TO_DATE ('12-JAN-2011');
END
;
If the string you provide does not match the default format, Oracle Database will raise an exception:
DECLARE
l_date DATE;
BEGIN
l_date := TO_DATE ('January 12 2011');
END;
/
ORA-01858: a non-numeric character was
found where a numeric was expected
You should not assume that the literal value you provide in your call to TO_DATE matches the default format. What if the format changes over time Instead, always provide a format mask when converting strings to dates, as in
l_date := TO_DATE ('January 12 2011',
'Month DD YYYY');
Date truncation. Use the TRUNC built-in function to truncate a date to the specified unit of measure. The most common use of TRUNC is TRUNC (date)—without any format mask specified. In this case, TRUNC simply sets the time to 00:00:00. You can also use TRUNC to easily obtain the first day in a specified period. Here are some TRUNC examples:
Set l_date to today’s date, but with the time set to 00:00:00:
l_date := TRUNC (SYSDATE);
Get the first day of the month for the specified date:
l_date := TRUNC (SYSDATE, 'MM');
Get the first day of the quarter for the specified date:
l_date := TRUNC (SYSDATE, 'Q');
Get the first day of the year for the specified date:
l_date := TRUNC (SYSDATE, 'Y');
Date a