Specify the format of the input data using y or Y for years, m or M for months, d or D for days, and j or J for Julian days. The length of the format string is the width of the input column. Table 7-6 describes the formatting options.
Option |
Meaning |
|---|---|
yyyy or YYYY yy or YY |
Represents number of year. Default is 1900. |
mm or MM |
Represents number of month. Always use leading zeros for number of the month where appropriate, for example '05' for May. If you omit the month from a DATE value, the day is treated as a Julian date. If you enter only the month, for example, '03', Sybase IQ applies the default year and day and converts it to '1900-03-01'. |
dd or DD jjj or JJJ |
Represents number of day. Default day is 01. Always use leading zeros for number of day where appropriate, for example '01' for first day. J or j indicates a Julian day (1 to 366) of the year. |
On input, the case the format code is ignored.
On output, the case of the format code has the following effect:
Mixed case (for example, “Dd”) means do not pad with zeroes.
Same case (for example, “DD” or “dd” means do pad with zeroes.
For example, a time as 17:23:03.774 using the default time format, but as 17:23:3.774 using 'HH:NN:Ss.SSS'.
The next table shows examples of how date input data looks and how to specify the format with the DATE conversion option. Following the table are general rules for specifying dates.
Input Data |
Format Specification |
|---|---|
12/31/09 |
DATE ('MM/DD/YY') |
12-31-09 |
DATE ('MM-DD-YY') |
20091231 |
DATE ('YYYYMMDD') |
12/09 |
DATE ('MM/YY') |
2009/123 |
DATE ('YYYY/JJJ') |
The DATE specification must be in parentheses and enclosed in single or double quotes.
Sybase IQ stores only the numbers of the year, month, and day; it does not store any other characters that might appear in the input data. However, if the input data contains other characters, for example, slashes (/), dashes (-), or blanks to separate the month, day, and year, the DATE format must show where those characters appear so they can be ignored.
Use any character other than Y, M, J, or D to indicate the separator character you want Sybase IQ to skip over. You can even use blanks.
If a DATE format includes only a year and a day number within the year, Sybase IQ treats the date as a Julian date. For example, 2009-33 is the 33rd day in the year 2009, or February 2, 2009.
If a year is specified with only two digits, for example “5/27/32”, then Sybase IQ converts it to 19yy or 20yy, depending on the year and on the setting of the NEAREST_CENTURY option.
NEAREST_CENTURY setting |
Year specified as |
Years assumed |
|---|---|---|
Default (50) |
00-49 50-99 |
2000-2049 1950-1999 |
0 |
any |
1900s |
100 |
any |
2000s |
For more information, see Chapter 2, “Database Options,” in Reference: Statements and Options.
