|
Specify the format used for converting timestamp values to strings.
timestamp_format=valueString
YYYY-MM-DD HH:NN:SS.SSS
For Android devices, use Connection.setOption(OPTION_TIMESTAMP_FORMAT, value) as an alternative to setting this creation option.
The default timestamp format YYYY-MM-DD HH:NN:SS.SSS conforms to ISO 8601. For example, "January 7, 2006 12:34 AM" in this format is presented as "2006-01-07 00:34:00.000". You can specify a different format and order for year, month, day, and time parts.
The format is a string composed of 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[.ssssss] | 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 timestamp_format creation option 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.
For symbols that represent character data (such as MMM), 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.
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.
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.
From SQL Central, you can set the timestamp_format option in any wizard that creates a database. On the New database creation parameters page, click the Timestamp Format option.
From a client application, set this option as one of the creation options for the CreateDatabase method on the DatabaseManager/ULDatabaseManager class.
The following command creates a database and sets the timestamp_format creation option so that the year is displayed in two digits and fractions of a second are excluded when retrieving TIMESTAMP values from the database:
ulinit --timestamp_format="YY-MM-DD HH:NN:SS" example.udb
Execute the following query on the created database:
SELECT CAST(CAST('Friday May 12, 2006 3:30 PM' AS TIMESTAMP) AS CHAR(32))The query returns 06-05-12 15:30:00.