Oracle Database now offers several functions
to provide variations of this information, as
shown in Table 1.
Listing 2 displays the values returned by
calls to SYSDATE and SYS TIMES TAMP.
Because I have passed dates and time
stamps to DBMS_OUTPUT.PUT_LINE,
Oracle Database implicitly converts them
to strings, using the default format masks
for the database or the session (as specified
by the National Language Settings NLS_
DATE_FORMAT parameter). A default installation of Oracle Database sets the default
DATE format to DD-MON-YY Y Y. The default
TIMES TAMP format includes both the date
offset and the time zone offset.
Note that it is possible to perform date
arithmetic: I subtract the value returned by
SYSTIMESTAMP from the value returned by
SYSDATE. The result is an interval that is very
close (but not quite equal) to zero.
Converting dates to strings and strings to
dates. As with TO_CHAR for numbers, you
use another version of the TO_CHAR function
to convert a date or a time stamp to a string.
And, again as with numbers, Oracle Database
offers a large set of format elements to help
you tweak that string so it appears exactly as
you need it. Here are some examples:
1. Use TO_CHAR without a format mask.
If you do not include a format mask,
the string returned by TO_CHAR will be
the same as that returned when Oracle
Database performs an implicit conversion:
/
Sunday , 07TH August 2011
Note: The language used to display
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,
'FMDay, DDth Month YYYY'));
END;
/
Sunday, 7TH August 2011
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE,
'Day, DDth Month YYYY',
'NLS_DATE_LANGUAGE=Spanish'));
END;
/
Domingo , 07TH Agosto 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:
1. What quarter is it?
TO_CHAR (SYSDATE, 'Q')
2. What is the day of the year (1–366) for
today’s date?
3. 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:
TO_CHAR (SYSDATE, 'DDD')
3. 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 (
BEGIN
DBMS_OUTPUT.put_line (
END;
TO_CHAR (SYSDATE));
DBMS_OUTPUT.put_line (
TO_CHAR (SYSTIMESTAMP));
Table 1: S YSDATE and other options for working with the current date and time
Function Time Zone Datatype Returned
CURRENT_DATE Session DATE
CURREN T_ TIMESTAMP Session TIMESTAMP WI TH TIME ZONE
LOCALTIMESTAMP Session TIMESTAMP
SYSDATE Database server DATE
SYSTIMESTAMP Database server TIMESTAMP WI TH TIME ZONE
Code Listing 2: Calls to SYSDATE and SYSTIMESTAMP and the returned values
2. Use TO_CHAR to display the full names of
both the day and the month in the date:
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE,
'Day, DDth Month YYYY'));
END;
BEGIN
DBMS_OUTPUT.put_line (SYSDATE);
DBMS_OUTPUT.put_line (SYSTIMESTAMP);
DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);
END;
/
Here is the output:
07-AUG- 11
07-AUG- 11 08.46.16.379000000 AM -05:00
