Getting the Current Date into a Header

You saw how to display the contents of a substitution variable in the header back in Chapter 5 when the Project Hours and Dollars Detail report was converted to a master/detail style. You saw how to use the COLUMN command to tell SQL*Plus to continuously update the contents of a substitution variable with the value of a column in the query. Getting the system date to display in the header involves a little trick that takes advantage of this use of the COLUMN command. The trick is to execute a query that returns the current date and use the NEW_VALUE clause of the COLUMN command to get that date into a substitution variable. That substitution variable sticks around for the duration of the session and can be used in subsequent reports .

7.2.1 Getting the Date from Oracle

I use the built-in SYSDATE function in the following example to return the current date from the database. Notice that the NEW_VALUE option of the COLUMN command is used to update the user variable report_date with the current value of SYSDATE as returned from the database.

COLUMN SYSDATE NEW_VALUE report_date SELECT SYSDATE FROM DUAL;

SYSDATE is an Oracle built-in function that returns the current date and time. DUAL is a special Oracle table that always exists, always contains exactly one row, and always contains exactly one column. You can select SYSDATE from any other table, but DUAL works well because it returns only one row, which is all you need to return the date.

7.2.2 Formatting the Date

You may find that the date format returned by SYSDATE is not what you would prefer. It depends on the setting of the NLS_DATE_FORMAT parameter, which can vary from one database to the next . You can use the ALTER SESSION statement to specify a different format:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';

ALTER SESSION changes the format for the duration of the SQL*Plus session. Make sure you execute it prior to selecting SYSDATE from DUAL. Another alternative is to use the built-in TO_CHAR function to specify a format.

COLUMN current_date NEW_VALUE report_date SELECT TO_CHAR(SYSDATE,'DD-Mon-YYYY') current_date FROM DUAL;

I specify a column alias of current_date in this example to give a usable name to the date column, one that could be used easily with the COLUMN command.

Table 7-1 shows some typical date format strings that may be used with Oracle's built-in TO_CHAR function or with the ALTER SESSION statement.

Table 7-1. Date format strings

You may or may not care whether the output of the SELECT SYSDATE statement appears on the display, but you can suppress it by using the SET TERMOUT command to toggle the display output off and then back on again. Here's how to do that:

SET TERMOUT OFF ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY'; COLUMN SYSDATE NEW_VALUE report_date SELECT SYSDATE FROM DUAL; SET TERMOUT ON

Finally, you need to add the date to the report header or the report footer. Here's an example of how to do that using the BTITLE command from the Project Hours and Dollars Detail report:

BTITLE LEFT '=============================================================' - SKIP 1 - LEFT report_date - RIGHT 'Page ' FORMAT 999 SQL.PNO

The addition of LEFT report_date to this BTITLE command causes the date to print left-justified on the same line as the page number. When you execute the report, the page footer will look like this:

======================================================================= 22-Feb-1998 Page 1

In addition to using this technique with the system date, you can use it to retrieve any other value from the database for inclusion in a report.