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.
Mixed case (for example, “Dd”) means do not pad with zeros.
Same case (for example, “DD” or “dd”) means do pad with zeros.
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') |
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 |