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-186
TO_CHAR(<DATETIME>) Function
is a constant string that defines the output format. NULL, column name, expression, or
dynamic parameter cannot be specified as format-value . The following table lists
all the datetime elements that can be used to construct the datetime format-values.
Element Description
datetime-
expression
format-
value
Output
-
/
,
.
;
:
“text”
Returns datetime
field separator or
punctuation in the
output format. The
text inside the
quotation marks (“ ”)
is reproduced
without any change
after removing the
quotes.
31-MAR-11
05.02.31.123457 AM
‘DD/MM/YY
YY’
‘31/03/2011’
AD
A.D.
BC
B.C.
Returns Anno
Domini or Before
Christ indicator with
or without periods.
31-MAR-11
05.02.31.123457 AM
‘YYYY AD’ ‘2011 AD’
AM
A.M.
PM
P. M.
Returns Ante
Meridian or Post
Meridian indicator
with or without
periods.
31-MAR-11
05.02.31.123457 AM
‘HH:M1 AM’ ‘05:02 AM’
CC
SCC
Returns Century 31-MAR-11
05.02.31.123457 AM
‘CC’ ‘21’
D Returns the day of
week (1-7)
Sunday = 1
31-MAR-11
05.02.31.123457 AM
‘D’ ‘5’
DAY
day
Day
Returns the name of
day in uppercase,
lower case, or
capitalized based
on the case of the
element, padded
with blanks to
display width of the
widest name of the
day.
31-MAR-11
05.02.31.123457 AM
‘DAY’
‘day’
’Day’
‘THURSDA
Y’
‘thursday’
‘Thursday’
DD Returns the day
(date) of month (1-
31)
31-MAR-11
05.02.31.123457 AM
‘DD’ ‘31’
DDD Returns the day of
year (1-366)
31-MAR-11
05.02.31.123457 AM
‘DDD’ ‘090’