SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-004
8-189
Considerations
Considerations
•
The length of the datetime format-value must not exceed 74 characters.
•
For elements whose output can be produced in uppercase, lowercase, or
capitalized (the first character in uppercase and rest in lowercase), the output is
based on the case of the element, for example:
°
TO_CHAR(timestamp‘2011-03-31 05.02.31.123457’, ‘DAY’) produces an
output ‘THURSDAY ’.
°
TO_CHAR(timestamp‘2011-03-31 05.02.31.123457’, ‘day’) produces an output
‘thursday ’.
°
TO_CHAR(timestamp‘2011-03-31 05.02.31.123457, ‘Day’) produces an output
‘Thursday ’.
•
The space character is used to fill the output value of an element to a constant
width equal to the largest element for the relevant format-value , for example:
WEDNESDAY has the maximum number of characters. Therefore, all the DAY
Y,YYY Returns the year
with a comma at the
specified position.
31-MAR-11
05.02.31.123457 AM
‘Y,YYY’ ‘2,011’
YYYY
SYYYY
Returns four digits
year; S prefixes BC
dates with a minus
sign.
31-MAR-11
05.02.31.123457 AM
‘YYYY’ ‘2011’
YYY
YY
Y
Returns the last
three digits; two
digits; or last digit of
the year.
31-MAR-11
05.02.31.123457 AM
‘YY’ ‘11’
WW Returns the week of
the year (1-53)
31-MAR-11
05.02.31.123457 AM
‘WW’ ‘13’
W Returns the week of
the month (1-5)
31-MAR-11
05.02.31.123457 AM
‘W’ ‘5’
th Adds the suffix
st/nd/th to the
numeric output
format.
Note: The ‘th’ suffix
can be added to any
element; the suffix
st/nd/th will be
ignored for non-
numeric output
value.
31-MAR-11
05.02.31.123457 AM
‘DDth’
‘DYth’
‘31st’
‘THU’
Element Description
datetime-
expression
format-
value Output