Specify the format of the DATETIME input data using:
Y or y for years
M or m for months
D or d for days
H or h to indicate hours
N or n to indicate minutes (mm is also accepted when colons are used as separators
S or s to indicate seconds and fraction of a second
The length of the format string is the width of the input column. Table 7-6 describes the date formatting options. The following table describes the time formatting options.
Option |
Meaning |
|---|---|
hh HH |
Represents hour. Hour is based on 24-hour clock. Always use leading zeros for hour where appropriate, for example '01' for 1 am. '00' is also valid value for hour of 12 am. |
nn |
Represents minute. Always use leading zeros for minute where appropriate, for example '08' for 8 minutes. |
ss[.ssssss] |
Represents seconds and fraction 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 Sybase IQ releases prior to 12.0; previously, pp was synonymous with aa.) |
hh |
Sybase IQ assumes zero for minutes and seconds. For example, if the DATETIME value you enter is '03', Sybase IQ converts it to '03:00:00.0000'. |
hh:nn or hh:mm |
Sybase IQ assumes zero for seconds. For example, if the time value you enter is '03:25', Sybase IQ converts it to '03:25:00.0000'. |
The Table 7-9 shows examples of how time input data may look and how to specify the format for the DATETIME option. Following this table are the general rules for specifying times.
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.
The input data can include up to 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, Sybase IQ always stores only six decimal positions with two positions for whole seconds (ss.ssssss). Any more 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).
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 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 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 a.m./p.m. is always indicated, while `pp' specifies that pm 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, Sybase IQ assumes 0 for each.
