Specifying the format for DATETIME conversions

Specify the format of the DATETIME input data using:

The length of the format string is the width of the input column. Table 7-10 describes the date formatting options. The following table describes the time formatting options.

Table 7-12: Formatting times

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-13 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.

Table 7-13: DATETIME format options

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