SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-45
Converting Timestamps
If you are formatting only a part of the date-time value, the applicable parts of the
format are used. For example, if you specify YEAR TO DAY in the DATEFORMAT
function, the EUROPEAN format will be dd.mm.yyyy.
These print items produce identical formats:
CURRENT
DATEFORMAT (CURRENT, DEFAULT)
A current date and time generated by either of these functions would appear as:
2000-04-15:18:22:05.61003
If you specify the print item:
DATEFORMAT (CURRENT, EUROPEAN)
the date and time would appear as:
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/MX Reference Manual.
To insert characters or spacing within a European date and time, you can include print
items in a report formatting command:
DATEFORMAT (CURRENT YEAR TO DAY, EUROPEAN), '*****',
DATEFORMAT (CURRENT HOUR TO MINUTE)
The result would appear as:
15.04.2000 ***** 18.22
Converting Timestamps
You can use the JULIANTIMESTAMP function to convert a value with an SQL/MX
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 display 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. For values of data type INTERVAL, you must use the default format shown in Ta bl e 4- 1
on page 4-31.