SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-43
Formatting Dates and Times
The following print items produce identical formats:
CURRENT
DATEFORMAT (CURRENT, DEFAULT)
A current date and time generated by either of these functions would appear as
follows:
2000-04-15:18:22:05.61003
If you specify the following print item:
DATEFORMAT (CURRENT, EUROPEAN)
the date and time would appear as follows:
15.04.2000 18.22.05.61003
You can request a different number of significant digits in the seconds value. For a
complete description of the CURRENT function, see the
SQL/MP Reference Manual.
If you want to insert characters or spacing within a European date and time, you can
include print items such as the following in a report formatting command:
DATEFORMAT (CURRENT YEAR TO DAY, EUROPEAN), "*****",
DATEFORMAT (CURRENT HOUR TO MINUTE)
The result would appear as follows:
15.04.2000 ***** 18.22
Converting Timestamps
You can use the JULIANTIMESTAMP function to convert a value with a NonStop
SQL/MP date-time data type to a Julian timestamp of data type LARGEINT. You can
use the CONVERTTIMESTAMP function to convert a LARGEINT timestamp value to a
value of data type TIMESTAMP. The availability of these conversion functions
increases your formatting options.
For example, you cannot format a TIMESTAMP value using the AS DATE or AS TIME
clause or the A descriptor of the AS clause, unless you use the JULIANTIMESTAMP
function to convert the value to a Julian timestamp. You might want to use this
conversion to print the name of the month instead of a number. For example, you can
specify the following print item to print a date, such as January 5, 2000:
JULIANTIMESTAMP (CURRENT) AS DATE "MA DB2, Y4"
Note that you do not need to include YEAR TO DAY with CURRENT because the AS
DATE clause selects only the month, day, and year from the timestamp.
Note. For values of data type INTERVAL, you must use the default format shown in Tabl e 4- 1
on page 4-28.