Specifying the DATE Format

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.

Table 7-6: Formatting dates

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:

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.

Table 7-7: Sample DATE format options

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')

For more information, see Chapter 2, “Database Options,” in Reference: Statements and Options.