SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-44
SQL/MX Date and Time Formats
Table 4-2 summarizes the characters you use to specify date and time formats in an
AS DATE/TIME clause, a DATE_FORMAT style option, or a TIME_FORMAT style
option.
You can format a column value with the AS DATE/TIME clause only if the value is
stored as a Julian timestamp of data type NUMERIC(18) or LARGEINT.
To display the current default date and time formats, enter:
>> SHOW STYLE DATE_FORMAT, TIME_FORMAT;
SQL/MX Date and Time Formats
If your report includes a date or time generated or stored as an SQL/MX date-time data
type, you must do one of the following:
•
Accept the default format for DATE, TIME, DATETIME, or TIMESTAMP data types,
as described in Table 4-1 on page 4-31.
•
Use the DATEFORMAT function to specify that you want the USA or EUROPEAN
version of the format for that data type. The formats produced by the
DATEFORMAT function for data types DATETIME and TIMESTAMP are listed in
Table 4-3. The data type of the value returned by DATEFORMAT is CHAR.
•
Convert the value to a Julian timestamp by using the JULIANTIMESTAMP function.
Table 4-2. Format Characters in AS DATE and AS TIME Clauses
Date Time
MMonth H Hour
DDay MMinute
Y Year S Second
A All characters of day or month C Hundredth of second
Ann characters only T Thousandths of second
B Suppress leading zeros P Hour as modulo 12 followed by AM or PM
O All digits of day B Suppress leading zeros
Onn digits only n Number of digits
n Number of characters or digits
Table 4-3. DATEFORMAT Display Formats
Format Name Format
DEFAULT yyy-mm-dd:hh:mm:ss.msssss
USA mm/dd/yyyy hh:mm:ss.msssss AM|PM
EUROPEAN dd.mm.yyyy hh.mm.ss.msssss