Specify the format of the DATETIME input.
Specify the format using:
- 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') |
General rules for specifying dates include:
- 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.