|
Specify the format used for converting time values to strings.
time_format=valueString (composed of the symbols listed below)
HH:NN:SS.SSS
For Android devices, use Connection.setOption(OPTION_TIME_FORMAT, value) as an alternative to setting this creation option.
UltraLite writes times from time parts you set with the time_format creation option. Time parts can include hours, minutes, seconds, and milliseconds.
TIME values can also be represented by strings. Before a time value can be retrieved, it must be assigned to a string variable.
UltraLite uses ISO 8601 as the default time standard. This international time standard indicates hours using the 24-hour clock system. For example, "midnight" in this international standard is written: 00:00:00. If you do not want to use the default time standard, you must specify a different format and order for these time parts.
The format is a string using the following symbols:
| Symbol | Description |
|---|---|
| HH | Two digit hours (24 hour clock). |
| NN | Two digit minutes. |
| MM | Two digit minutes if following a colon (as in HH:MM). |
| SS[.ssssss] | Seconds and fractions of a second, up to six decimal places. Not all platforms support timestamps to a precision of six places. |
You cannot change the time_format creation option of an existing database. Instead, you must create a new database.
Each symbol is substituted with the appropriate data for the time that is being formatted. Any format symbol that represents character rather than digit output can be put in uppercase, which causes the substituted characters to be in uppercase. For numbers, using mixed case in the format string suppresses leading zeros.
Control zero-padding with the case of the symbols:
Type the symbol in same-case (such as HH or hh) to allow zero padding. For example, HH:NN:SS could produce 01:01:01.
Type the symbol in mixed case (such as Hh or hH) to suppress zero padding. For example, Hh:Nn:Ss could produce 1: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 time_format option in any wizard that creates a database. On the New database creation parameters page, click the Time 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 time_format creation option so that fractions of a second are excluded when retrieving TIME values from the database:
ulinit --time_format=HH:NN:SS example.udb
Execute the following query on the created database:
SELECT CAST(CAST('3:30:12.345 PM' AS TIME) AS CHAR(32))The query returns 15:30:12.