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

SQL Anywhere 11.0.1 » UltraLite - Database Management and Reference » UltraLite Database Reference » UltraLite creation parameters


UltraLite date_format creation parameter

Sets the format for dates retrieved from the database.

{ ulcreate | ulinit | ulload } -o date_format=value;...
Allowed values



YYYY-MM-DD (this corresponds to ISO date format specifications)


DATE data type values are represented in a format set by the date_format creation parameter. Date values can, however, also be represented by strings. Before the value can be retrieved, it must be assigned to a string.

UltraLite builds a date from date parts. Date parts can include the year, the month, the day of the month, the day of the week, the day of the year, the hour, the minute, and the second (and parts thereof).

ISO (YYYY-MM-DD) is the default date format and order. For example, "7th of January 2006" in this international format is written: 2006-01-07. If you do not want to use the default ISO date format and order, you must specify a different format and order for these date 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. An uppercase M causes the output to be made uppercase.
d Single digit day of week, (0 = Sunday, 6 = Saturday).
dd Two digit day of month. A leading zero is not required.
ddd[d...] Character short form for day of the week. An uppercase D causes the output to be made uppercase.
hh Two digit hours. A leading zero is not required.
nn Two digit minutes. A leading zero is not required.
ss[.ss..] Seconds and parts of a second.
aa Use 12 hour clock. Indicate times before noon with AM.
pp Use 12 hour clock. Indicate times after noon with PM.
jjj Day of the year, from 1 to 366.

You cannot change the date format of an existing database. Instead, you must create a new database.

Allowed values are constructed from the symbols listed in the table above. Each symbol is substituted with the appropriate data for the date that is being formatted.

For the character short forms, the number of letters specified is counted. The A.M. or P.M. indicator (which could be localized) is also truncated, if necessary, to the number of bytes corresponding to the number of characters specified.

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

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

  • Type the symbol in lowercase to have the format appear in 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.

Controlling zero-padding   For symbols that represent numeric data, you can 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.

From Sybase Central, you can set the date format in any wizard that creates a database. On the New database creation parameters page, select the Date Format option.

From a client application, set this parameter as one of the creation parameters for the create database method on the database manager class.

See also

The following table illustrates date_format settings, together with the output from a SELECT CURRENT DATE statement, executed on Thursday May 21, 2001.

date_format syntax used Result returned
yyyy/mm/dd/ddd 2001/05/21/thu
jjj 141
mmm yyyy may 2001
mm-yyyy 05-2001