DATE_FORMAT Session Default

Syntax

SET SESSION DEFAULT DATE_FORMAT format-string;

Description

The DATE_FORMAT is a session default attribute which specifies the format string for converting the DATE type2 to a formatted string. The default format string is 'YYYY-MM-DD'. This session default is applicable only for DATE type2 datatype.

Parameters

format-string: Specifies a constant string that defines the output format. The following table lists the supported formats.

Format string elements
Element	Description	`datetime-expression`	`format-string`	Output
- / , . ; : “text”	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/YYYY’	‘31/03/2011’
AD A.D. BC B.C.	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.	Ante Meridian or Post Meridian indicator with or without periods.	31-MAR-11 05.02.31.123457 AM	‘HH:MM AM’	‘05:02 AM’
CC SCC	Century	31-MAR-11 05.02.31.123457 AM	‘CC’	‘21’
D	The day of week (1-7) Sunday = 1	31-MAR-11 05.02.31.123457 AM	‘D’	‘5’
DAY day Day	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’	‘THURSDAY’ ‘thursday’ ‘Thursday’
DD	The day (date) of month (1-31)	31-MAR-11 05.02.31.123457 AM	‘DD’	‘31’
DDD	The day of year (1-366)	31-MAR-11 05.02.31.123457 AM	‘DDD’	‘090’
DY dy Dy	The abbreviated name of the day in uppercase, lowercase, or capitalized based on case of the element.	31-MAR-11 05.02.31.123457 AM	‘DY’ ‘dy’ ‘Dy’	‘THU’ ‘thu’ ‘Thu’
FM	Fill-mode modifier is used to remove leading or trailing blanks.	31-MAR-11 05.02.31.123457 AM	‘FMMONTH’	‘MARCH’
HH HH12	The hour of the day (1-12)	31-MAR-11 05.02.31.123457 AM	‘HH’	‘05’
HH24	The hour of the day (0-23)	31-MAR-11 05.02.31.123457 AM	‘HH24’	‘05’
IW	The week of the year (1-52 or 1-53) based on the ISO standard.	31-MAR-11 05.02.31.123457 AM	‘IW’	‘13’
IYYY IYY IY I	Last 4, 3, 2 or 1 digits of the ISO year.	31-MAR-11 05.02.31.123457 AM	‘IYYY’	‘011’
YEAR year Year SYEAR Syear SYear	Spell out year in uppercase, lowercase, or capitalized based on the case of the element.	31-MAR-11 05.02.31.123457 AM	‘YEAR’ ‘year’ ‘Year’	‘TWENTY ELEVEN ‘twenty eleven’ ‘Twenty Eleven’
J	Julian day; the number of days since January 1, 4712 BC.	31-MAR-11 05.02.31.123457 AM	‘J’	‘2455652’
MI	Minutes (0-59)	31-MAR-11 05.02.31.123457 AM	‘MI’	‘02’
MM	Month (01-12; January = 01)	31-MAR-11 05.02.31.123457 AM	‘MM’	‘03’
MON mon Mon	The abbreviated name of month in uppercase, lowercase, or capitalized based on the case of the element.	31-MAR-11 05.02.31.123457 AM	‘MON’ ‘mon’ ‘Mon’	‘MAR’ ‘mar’ ‘Mar’
MONTH month Month	The name of month in uppercase, lowercase, or capitalized based on the case of the element, padded with blanks to display width of the widest name of a month.	31-MAR-11 05.02.31.123457 AM	‘MONTH’ ‘month’ ‘Month’	‘MARCH’ ‘march’ ‘March
RM	The month in roman numerals (I to XII).	31-MAR-11 05.02.31.123457 AM	‘RM’	‘III’
RR RRRR	The last two or four digits of the year.	31-MAR-11 05.02.31.123457 AM	‘RR’ ‘RRRR’	‘11’ ‘2011’
SS	Seconds (0-59)	31-MAR-11 05.02.31.123457 AM	‘SS’	‘31’
SSSSS	Seconds past midnight (0-86399).	31-MAR-11 05.02.31.123457 AM	‘SSSSS’	‘18151’
X	The local radix character. The ‘.’ (dot) is the only supported local radix character.	31-MAR-11 05.02.31.123457 AM	‘HH:MM:SSXFF’	‘05:03:31.123457’
Y,YYY	The year with a comma at the specified position.	31-MAR-11 05.02.31.123457 AM	‘Y,YYY’	‘2,011’
YYYY SYYYY	Four digits year; S prefixes BC dates with a minus sign.	31-MAR-11 05.02.31.123457 AM	‘YYYY’	‘2011’
YYY YY Y	The last three digits; two digits; or last digit of the year.	31-MAR-11 05.02.31.123457 AM	‘YY’	‘11’
WW	The week of the year (1-53)	31-MAR-11 05.02.31.123457 AM	‘WW’	‘13’
W	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’

Considerations

The length of format-string must not exceed 74 characters.
NULL, column name, expression, or dynamic parameter cannot be specified as a format-string.
DATE_FORMAT session default attribute is supported with mxci interface and JDBC T2 driver.

To use DATE_FORMAT from ODBC or JDBC T4 applications, CAST the DATE type2 as CHARACTER string with sufficient length to hold the formatted output.