Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

DATEFORMAT and TIMEFORMAT Strings

The DATEFORMAT and TIMEFORMAT options in the COPY command take format strings. These strings can contain datetime separators (such as '-', '/', or ':') and the following "dateparts" and "timeparts".

Note

If the COPY command does not recognize the format of your date or time values, or if your date and time values use formats different from each other, use the 'auto' argument with the TIMEFORMAT parameter. The 'auto' argument recognizes several formats that are not supported when using a DATEFORMAT and TIMEFORMAT string.

Datepart or Timepart Meaning
YY Year without century
YYYY Year with century
MM Month as a number
MON Month as a name (abbreviated name or full name)
DD Day of month as a number
HH or HH24 Hour (24-hour clock)
HH12 Hour (12-hour clock)
MI Minutes
SS Seconds
AM or PM Meridian indicator (for 12-hour clock)

The default date format is YYYY-MM-DD. The default time stamp without time zone (TIMESTAMP) format is YYYY-MM-DD HH:MI:SS. The default time stamp with time zone (TIMESTAMPTZ) format is YYYY-MM-DD HH:MI:SSOF, where OF is the offset from UTC (for example, -8:00. You can't include a time zone specifier (TZ, tz, or OF) in the timeformat_string. The seconds (SS) field also supports fractional seconds up to a microsecond level of detail. To load TIMESTAMPTZ data that is in a format different from the default format, specify 'auto'. For more information, see Using Automatic Recognition with DATEFORMAT and TIMEFORMAT.

For example, the following DATEFORMAT and TIMEFORMAT strings are valid.

COPY Syntax Example of Valid Input String
DATEFORMAT AS 'MM/DD/YYYY' 03/31/2003
DATEFORMAT AS 'MON DD, YYYY' March 31, 2003
TIMEFORMAT AS 'MM.DD.YYYY HH:MI:SS' 03.31.2003 18:45:05

03.31.2003 18:45:05.123456