Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » UltraLite - Database Management and Developer Guide » UltraLite database reference » UltraLite options

UltraLite timestamp_with_time_zone_format creation option

Specify the format used for converting TIMESTAMP WITH TIME ZONE values to strings.

Syntax
timestamp_with_time_zone_format=value
Allowed values

String (composed of the symbols listed below)

Default

YYYY-MM-DD HH:NN:SS.SSS+HH:NN

Remarks

The default format YYYY-MM-DD HH:NN:SS.SSS+HH:NN conforms to ISO 8601. You can specify a different format and order for year, month, day, time, and time zone parts.

The format is a string using the following symbols:

Symbol Description
YY Two digit year
YYYY Four digit year
MM Two digit month, or two digit minutes if following a colon (as in HH:MM).
MMM[m...] Character short form for months (as many characters as there are "m"s).
DD Two digit day of month.
DDD[d...] Character short form for day of the week.
HH Two digit hours.
NN Two digit minutes.
SS[.ssssss] Seconds and fractions of a second, up to six decimal places. Not all platforms support timestamps to a precision of six places.
AA A.M. or P.M. (12 hour clock). Omit AA and PP for 24 hour time.
PP P.M. if needed (12 hour clock). Omit AA and PP for 24 hour time.
HH Two digit hours (time zone offset).
NN Two digit minutes (time zone offset).

Each symbol is substituted with the appropriate data for the date that is being formatted.

For symbols that represent character data (such as MMM), you can control the case of the output as follows:

  • Type the symbol in all uppercase to have the format appear in all uppercase. For example, MMM produces JAN.

  • Type the symbol in all lowercase to have the format appear in all lowercase. For example, mmm produces jan.

  • Type the symbol in mixed case to have UltraLite choose the appropriate case for the language that is being used. For example, in English, typing Mmm produces May, while in French it produces mai.

  • If the first two digits of the fractional seconds are mixed case (such as Ss or sSssss) then trailing zeros are removed. For example, hh:nn:ss.Sss could produce 12:34:56.1.

If the character data is multibyte, the length of each symbol reflects the number of characters, not the number of bytes. For example, the MMM symbol specifies a length of three characters for the month.

For symbols that represent numeric data, control zero-padding with the case of the symbols:

  • Type the symbol in same-case (such as MM or mm) to allow zero padding. For example, yyyy/mm/dd could produce 2002/01/01.

  • Type the symbol in mixed case (such as Mm) to suppress zero padding. For example, yyyy/Mm/Dd could produce 2002/1/1.

  • If the first two digits of the fractional seconds are mixed case (such as Ss or sSssss), then trailing zeros are removed. For example, hh:nn:ss.Sss could produce 12:34:56.1.

Note If you change the setting for timestamp_with_time_zone_format option in a way that re-orders the date format, be sure to change the date_order option to reflect the same change, and vice versa.

Example

The following command creates a database and sets the timestamp_with_time_zone_format creation option so that the year is displayed in two digits and fractions of a second are excluded when retrieving TIMESTAMP WITH TIME ZONE values from the database:

ulinit --timestamp_with_time_zone_format="YY-MM-DD HH:NN:SS+HH:NN" example.udb

Execute the following query on the created database:

SELECT CAST(CAST('Friday May 12, 2006 3:30 PM -04:00' AS TIMESTAMP WITH TIME ZONE) AS CHAR(32))

The query returns 06-05-12 15:30:00-04:00.