Specify the format of the DATETIME input.
Y or y for years
M or m for months
D or d for days
H or h for hours
N or n for minutes (mm is also accepted when colons are used as separators)
S or s for seconds and fractions of a second
The length of the format string is the width of the input column.
Option |
Meaning |
|---|---|
hh HH |
Represents hour, based on 24-hour clock. Always use leading zeros for hour where appropriate, for example '01' for 1 a.m. '00' is also a valid value for 12 a.m. |
nn |
Represents minute. Always use leading zeros for minute where appropriate, for example, '08' for 8 minutes. |
ss[.ssssss] |
Represents seconds and fractions of a second. |
aa |
Represents the a.m. or p.m. designation. |
pp |
Represents the p.m. designation only if needed. (This is incompatible with SAP Sybase IQ versions earlier than12.0; previously, pp was synonymous with aa.) |
hh |
SAP Sybase IQ assumes zero for minutes and seconds. For example, if the DATETIME value you enter is '03', SAP Sybase IQ converts it to '03:00:00.0000'. |
hh:nn or hh:mm |
SAP Sybase IQ assumes zero for seconds. For example, if the time value you enter is '03:25', SAP Sybase IQ converts it to '03:25:00.0000'. |
Sample DATETIME format options show how time input data may look and how to specify the format for the DATETIME option.
Input Data |
Format Specification |
|---|---|
12/31/00 14:01:50 |
DATETIME ('MM/DD/YY hh:nn:ss') |
123100140150 |
DATETIME ('MMDDYYhhnnss') |
14:01:50 12-31-00 |
DATETIME ('hh:mm:ss MM-DD-YY') |
12/31/00 14:01:12.456 |
DATETIME ('MM/DD/YY hh:nn:sssssss') |
12/31/00 14:01:.123456 |
DATETIME ('MM/DD/YY hh:mm:sssssss') |
12/31/00 02:01:50AM |
DATETIME ('MM/DD/YY hh:mm:ssaa') |
12/31/00 02:01:50pm |
DATETIME ('MM/DD/YY hh:mm:sspp') |
Specification letters for time components must be in enclosed in parentheses and single or double quotation marks.
Input data can include as many as nine positions for seconds, including a floating decimal point, to allow for fractional seconds. On input and query, the decimal point floats, so you can specify up to six decimal positions. However, SAP Sybase IQ always stores only six decimal positions with two positions for whole seconds (ss.ssssss). Additional decimal positions are not permitted.
Separators are used between the time elements. You can use any character as a separator, including blanks. The example uses colons.
SAP Sybase IQ stores only the numbers of hours, minutes, and seconds; it does not store any other characters which might appear in the input data. However, if the data contains other characters, for example colons or blanks to separate hours, minutes, and seconds, the time portion of the format specification must show where those characters appear so that SAP Sybase IQ knows to skip over them.
To indicate whether a particular value is a.m. or p.m., the input data must contain an upper- or lowercase 'a' or 'p' in a consistent place. To indicate where SAP Sybase IQ should look for the a.m. or p.m. designation, put a lowercase only 'aa' or 'pp' in the appropriate place in the format specification. `aa' specifies that a.m./p.m. is always indicated, while `pp' specifies that p.m. is indicated only if needed.
The format specification must have a character to match every character in the input; you cannot have an 'm' in the format specification to match the 'm' in the input, because 'm' is already used to indicate minutes.
In the time section, when hours or minutes or seconds are not specified, SAP Sybase IQ assumes 0 for each.