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.
| 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', SAP 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 of the format code is ignored.
On output, the case of the format code has the effect:
- Mixed case (for example, “Dd”) means do not pad with zeros.
- Same case (for example, “DD” or “dd”) means do pad with
zeros.
For example, a time is output as 17:23:03.774 using the default time format, but as
17:23:3.774 using 'HH:NN:Ss.SSS'.
Sample DATE format options show how date input data look and how to specify the
format with the DATE conversion option.
| 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') |
General rules for specifying dates include:
- The DATE specification must be in parentheses and enclosed in
single or double quotes.
- SAP 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 SAP 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, SAP 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 SAP 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 |