SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-42
Formatting Dates and Times
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.
You can display the current default date and time formats by entering the following:
>> SHOW STYLE DATE_FORMAT, TIME_FORMAT;
Formats for NonStop SQL/MP
If your report includes a date or time generated or stored as one of the NonStop
SQL/MP date-time data types, 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-28.
•
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 shown 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.
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.
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 Hundreth 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