SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-185
Examples of TO_CHAR(<DATETIME>)
output formats will include 9 characters. If the output value is Tuesday, two space
characters are padded to the right (‘TUESDAY ’).
•
Fill Mode (FM) modifiers are used to control blank padding, for example:
TO_CHAR(timestamp‘2011-03-31 05.02.31.123457’, ‘FMDay’) produces the output
‘THURSDAY’.
•
The elements CC, DDD, DD, D, HH24, HH12, HH, IW, IYYY, IYY, IY, I, J, MI, MM,
SSSSS, SS, WW, W, Y, YYYY, YYY, YY, Y, SCC, FF[1..9], RRRR, RR, SYYYY, and
X are case-insensitive.
•
The suffix element ‘th’ is ignored for non-numeric output, for example:
°
TO_CHAR(timestamp‘2011-09-28 15:15:10.599494’, ‘DDth’) produces 28th as
output.
°
TO_CHAR(timestamp‘2011-09-28 15:15:10.599494’, ‘Dayth’) produces
‘Wednesday’ as output. Notice that ‘th’ is ignored.
Examples of TO_CHAR(<DATETIME>)
TRANSLATE Function
The TRANSLATE function translates a character string from a source character set to
a target character set.
character-value-expression
is a character string.
Expression Result
TO_CHAR(timestamp
‘2011-09-28 15:15:10.599494’, ‘Day,
DD HH12:MI:SS’)
‘Wednesday, 28 03:15:10’
TO_CHAR(timestamp
‘2011-09-28 15:15:10.599494’,
‘“Day:”Day’)
‘Day:Wednesday’
TO_CHAR(timestamp
‘2011-09-28 15:15:10.599494’, ‘DAY’) ‘WEDNESDAY’
TO_CHAR(timestamp
‘2011-09-28 15:15:10.599494’, ‘DD-
MM-YY’)
‘28-09-2011’
TO_CHAR(timestamp
‘2011-09-28 15:15:10.599494’,
‘HH:MI AM’)
‘03:15 PM’
TRANSLATE(character-value-expression USING translation-name)